MS SQL Bridge Table Constraints

Question

Greetings -

I have a table of Articles and a table of Categories.

An Article can be used in many Categories, so I have created a table of ArticleCategories like this:

• BridgeID int (PK)

• ArticleID int

• CategoryID int

Now, I want to create constraints/relationships such that the ArticleID-CategoryID combinations are unique AND that the IDs must exist in the respective primary key tables (Articles and Categories).

I have tried using both VS2008 Server Explorer and Enterprise Manager (SQL-2005) to create the FK relationships, but the results always prevent Duplicate ArticleIDs in the bridge table, even though the CategoryID is different.

I am pretty sure I am doing something obviously wrong, but I appear to have a mental block at this point.

Can anyone tell me please how should this be done?

Greaty appreciated!

Answer 1

Don't use a BridgeId column.

Make a composite primary key (aka compound key) from your ArticleId and CateogryId, that will ensure each combination is unique.

Make each column a foreign key to the corresponding table, and that completes your set of constraints.

Answer 2

Ok first you do a unique index on ArticleID, CategoryID.

Then you set up a foreign key constraint on articleID linking it back to the Article table. and then do the same for CategoryID and Catgory table.

Your description sounds like you are creating the PK on the Bridge table and the FK on the other table which is why it wouldn't work.

Answer 3

Expanding on HLGEM's solution you would have something like:

Create Table ArticleCategories
(
    Id int not null Primary Key Clustered
    , ArticleId int not null
    , CategoryId int not null
    , Constraint UK_ArticleCategories_Unique ( ArticleId, CategoryId )
    , Constraint FK_ArticleCategories_Articles
        Foreign Key ( ArticleId )
        References Articles( Id )
    , Constraint FK_ArticleCategories_Categories
        Foreign Key ( CategoryId )
        References Categories( Id )
)