Should my intermediate table contain a composite PK or a unique index
Let's say I have a Products
table, ProductsCategory
table and a Category
table.
The ProductsCategory
table has two columns: Produc开发者_运维百科tID
and CategoryID
. Should I be using a composite primary key or a unique index on the two columns?
Additionally if I use the index, I can make it a unique index or a key.
Might as well use a composite key - no need to add a unique index when you already have the uniqueness semantics of a composite primary key.
You must create two way foreign key both table with on delete cascade option. Because if you delete one of categories then it must remove relational rows on ProductCategory.
i mean you can use like this :
alter table ProductsCategory add constraint ForeignKey1 foreign key (ProductId) references Products (ID) ON DELETE CASCADE;
alter table ProductsCategory add constraint ForeignKey2 foreign key (CategoryId) references Category (ID) ON DELETE CASCADE;
精彩评论