SQL Server (2008) Creating link tables with unique rows

Question

Hi guys, I'm having trouble getting in touch with SQL Server Managemen Studio 2008! I want to create a link-table that will link an Event to many Audiences (EventAudience).

An example of the data that could be contained:

EventId | AudienceId

  4              1
  5              1
  4              2

However, I don't want this:

EventId | AudienceId

  4              1
  4              1

I've been looking at relationships and constraints.. but no joy so far!

As a sneaky second part to the question, I would like to set up the Audience table such that if a row is deleted from Audience, it will clear down the EventAudience link table in a cascading manner.

As always, ANY help/advice appreciated!

Thanks Pete

Answer 1

Just use:

CREATE UNIQUE INDEX UQ_EventAudience ON dbo.EventAudience(EventID, AudienceID);

Answer 2

You should be able to create a composite UNIQUE constraint on both EventId and AudienceID.

And a foreign key relationship between this table and the Audience table that has a CASCADE DELETE should take care of your second part.

Answer 3

Alternate suggestion : Use triggers.

pseudocode...

Create on-update trigger on link table...
if record doesn't already exist create it, else exit.

Same idea for the deletion

pseudocode again

Create on-delete trigger on audience table...
delete the item from the audiences table
locate and delete references in the link table.

Sorry it's on pseudocode, I don't have SQL server close at hand to look up syntax so I didn't want to mislead.

Answer 4

@Joe - Please ignore that FK constraint sorted it out! Thanks!

Answer 5

Could also make these ids the compound primary key for the table. Since uniqueness is required of primary keys.

CONSTRAINT [PK_EventIdAudienceId] PRIMARY KEY CLUSTERED ( [EventId] ASC, [AudienceId] ASC )

Answer 6

If you're editing in your table designer you can ctrl click both columns and set as primary key. Thats always seemed to work for me.