Home » SQL & PL/SQL » SQL & PL/SQL » Design issue (11.2.0.3.0)
Design issue [message #687575] Fri, 07 April 2023 17:37 Go to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

Hi all,

I have ths schema :
drop table txt cascade constraints;
create table txt
(
	id_txt		number
);
alter table txt add constraint pk_txt primary key (id_txt);

insert into txt values (100);
insert into txt values (200);

-- =============================
drop table item cascade constraints;
create table item
(
	id_item		number	,
	id_txt1		number	,
	id_txt2		number
);

alter table item add constraint pk_item primary key (id_item)
deferrable
initially deferred
;
-- =============================
drop table item_txt cascade constraints;
create table item_txt
(
	id_item		number	,
	txt_type	number	,
	id_txt		number not null
);

alter table item_txt add constraint pk_item_txt primary key (id_item, txt_type)
deferrable
initially deferred
;
alter table item_txt add constraint pk_item_txt_txt foreign key (id_txt)
references txt(id_txt)
deferrable
initially deferred
;
-- =============================
insert into item values (1, 100, 200);

insert into item_txt values (1, 1, 100);
insert into item_txt values (1, 2, 200);

commit;

-- =============================
Now I want to add two (02) foreign keys on item
1. the couple (id_item, id_txt1) references item_txt(id_item, id_txt) where txt_type equals 1
2. the couple (id_item, id_txt2) references item_txt(id_item, id_txt) where txt_type equals 2.

How can I achieve that ?

Re: Design issue [message #687577 is a reply to message #687575] Fri, 07 April 2023 19:09 Go to previous messageGo to next message
mathguy
Messages: 106
Registered: January 2023
Senior Member
The question doesn't make sense.

For <whatever> to be a foreign key referencing <something>, it must be the case that <something> is a unique key in its table. That is by definition; we aren't allowed to make up our own definition for a well established term like "foreign key".

The only constraint you have on item_txt is that (id_item, txt_type) must be primary key. For all we know, all values of id_txt in this table, for all item id's and all txt types, may be equal to each other; the opposite of uniqueness. So, what kind of "foreign key" are you trying to add? Or do you, in fact, assume that (id_item, id_txt) in item_txt are all distinct when txt_type = 1, and then the same again when txt_type = 2? If so, then you should first ask how to enforce those constraints (on a single table - never mind constraints involving more than one table).

Or do you simply want to check that the values of (id_item, id_txt1) are found in the form (id_item, 1, id_txt) in table item_txt (and similar for 2 instead of 1)? That is fine, but that isn't a "foreign key" constraint - not unless the values in table item_txt are distinct as described above.

So - please clarify.
Re: Design issue [message #687578 is a reply to message #687577] Fri, 07 April 2023 19:59 Go to previous messageGo to next message
Amine
Messages: 371
Registered: March 2010
Senior Member

mathguy wrote on Sat, 08 April 2023 01:09

Or do you simply want to check that the values of (id_item, id_txt1) are found in the form (id_item, 1, id_txt) in table item_txt (and similar for 2 instead of 1)? That is fine, but that isn't a "foreign key" constraint - not unless the values in table item_txt are distinct as described above.
That's it.

Actually I missed a unique constraint :

alter table item_txt add constraint uk_item_txt unique (id_item, txt_type, id_txt);
I thnik I can achieve it by creating an materialized view like this one
select id_item
, 1 txt_type1
, id_txt1
, 2 txt_type2
, id_txt2
from item
;
and then ensure that the tuple (id_item, 1, id_txt1) exists in item_txt (id_item, txt_type, id_txt)
and the tuple (id_item, 2, id_txt2) exists in item_txt (id_item, txt_type, id_txt).

Unfortunetely, I am running Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production.

I get this
ORA-00439: feature not enabled: Materialized view rewrite
when trying to create a materialized view log.

Am I right, or could we do better ?
Re: Design issue [message #687584 is a reply to message #687578] Sat, 08 April 2023 17:50 Go to previous message
mathguy
Messages: 106
Registered: January 2023
Senior Member
OK -

I realized my comment wasn't entirely correct, because the uniqueness you need is for pairs (id_item, id_txt) where txt_type = 1 in ITEM_TXT (and similar for txt_type = 2), and this uniqueness is already guaranteed because (id_txt, txt_type) is primary key in that table.

There is a way to enforce your additional constraints without materialized views, and I think it should work in version 11. One thing that is needed is the unique constraint on (id_item, txt_type, id_txt), and you just said that there is, in fact, such a constraint. This may seem a bit strange - since (id_item, txt_type) is already unique (primary key!), why do you also need to explicitly add this weaker uniqueness on all three columns? The answer is that, in order to enforce a FK constraint referencing all three columns, Oracle needs an index on the referenced columns. The PK constraint uses an index just on the two PK columns, so that index can't be used to verify the FK constraints (if any) on all three columns. So, even though it seems strange, the weaker constraint must be declared explicitly.

Now on table ITEM: you need to add two virtual columns (which existed already in version 11 of the database) - with constant values, 1 and 2 respectively - and then create FK constraints using these virtual columns. Note that the virtual columns take a minimal amount of space in the meta-data (the data catalog) and no "data" space, and the calculation at runtime is trivial, since the values are hard-coded constants.

alter table item add (typ1 number as (1));
alter table item add (typ2 number as (2));
Note that this approach will make things like SELECT * FROM ITEM work in an unexpected way: the virtual columns will also be included in the output. To avoid that, you would need to name columns explicitly in the SELECT clause - which is a best practice anyway. Oracle 12 introduced the INVISIBLE modifier (exactly to "fix" this type of problem), but that won't work in Oracle 11.

Then add the FK constraints:

alter table item add constraint fk_item_type_1
      foreign key (id_item, typ1, id_txt1)
      references item_txt(id_item, txt_type, id_txt);
      
alter table item add constraint fk_item_type_2
      foreign key (id_item, typ2, id_txt2)
      references item_txt(id_item, txt_type, id_txt);
Previous Topic: difficult task
Next Topic: Oracle Style with Table Alias
Goto Forum:
  


Current Time: Fri Mar 29 06:58:40 CDT 2024