New answers tagged referential-integrity
2
With the clarifications in your comments, that there are only 2 levels of products, e.g. only Products and SubProducts and no subproduct is related to two or more products, your design is fine.
I would only add two unique constraints on table SubProducts, on (IDproduct, IDsubProduct) and on (IDproduct, Name) - or make one of them PRIMARY KEY and the other ...
0
A hierarchical relationship is probably fine:
PRODUCT
id
name
parent_id (nullable)
insert into product values (1, 'Proc A');
insert into product values (2, 'SubProA1', 1);
For performance reasons, you will need special queries. I'd use postgres over mysql if I were you. Use WITH queries.
1
You can add Table4.id4, then you can replace Table1.id2 , Table1.id3 with Table1.id4 which references Table4.
Or you can enforce constraint by creating indexed view.
1
There's a way to enforce that Table1.id3 only can seted to values in Table4.id3 where Table4.id2 = Table1.id2?
It seems to me like you could just set a foreign key constraint directly to Table4.
alter table Table1
add constraint your_constraint_name
foreign key (id2, id3) references Table4 (id2, id3);
You might have to jump through a hoop or two to ...
Top 50 recent answers are included
