SQL many to many realtion table, but unique entires

Question

Here is a nice underhand lob pitch to you guys.

So basically I've got my content table with unique primary key IDs and I've got my tag table with unique primary key IDs.

I've got a table that has an identity column as a primary key but the two other columes are the contentID and tagID. What do I need to do to the table to make sure that I only have the same contentID and tagID combo only once.

Answer 1

You put a Unique constraint on contentID, tagID.

For SQL Server

ALTER TABLE ContentTag ADD CONSTRAINT
    IX_ContentID_TagID_Unique UNIQUE NONCLUSTERED ( contentID, tagID ) 
GO

Answer 2

There's no reason from a data modeling perspective that you need an identity column in the mapping table. The primary key constraint should be a two-column constraint over contentID, tagID.

Some frameworks (e.g. Rails) demand that every table have a surrogate key named id, even if it makes no sense for the entity relationship. This is a pity, but apparently this convention gives them some efficiency somewhere else.

You can always create a UNIQUE constraint over contentID, tagID in addition to the table's primary key on the surrogate key column.

Answer 3

There is one reason to have a numeric (surrogate) key on a many-to-many join table... That is, when there are other child derived tables that would need Foreign Keys back to the records in this table... i.e., say you have Table PetStores w/StoreId, table Breeds w/BreedId, and many-to-many join table StoreBreeds that has a row for each store=breed combination... And then let's say you want to track the price, and Store Discount for each breed at each stoer, as it has changed over time, So you need another table to record the price of each breed at each store, with valid start and end dates reflecting the range of dates when the price was in effect for that breed at that store..

if you only haD a meaningful composite key for the many to many join table, then the FK in the child table would have to be composite as well, based on StoreId and BreedId... For performance, adding a non-meaningful integral surrogate key to the join table allows you to use that as the FK in derived child tables instead, and thereby increase the performance of joins to retrieve those child records...

In a simple case, this may not be that significant, but in more complex scenarios, where the composite key consists of 4 or more columns, the impact can be substantial.

Answer 4

Consider this problem:

Table A has 2 child tables ( B & C )

The PK of B and C is an identity, and they both have a FK back to Table A.

I have a table D, which is a join table on B and C

Table D has PK of an identity and FK's back to B and C.

A ..... two rows ... A1 and A2

B ... two rows ... B1 (FK to A1) & B2 (FK to A2)

C ... two rows .... C1 (FK to A1) & C2 (FK to A2)

D ..... I can add the following to D .... FK to B1 and FK to C1 ..... this is wrong.

I want to make sure that whatever B and C are referenced in D both point back to same A !!!

With composite keys this is possible .... but I can’t see how it can be done in the database with identities being user everywhere.

Alan