Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Firstly, I know my title is somewhat misleading, but I couldn't think of a better title.

Here is my current relationship

CREATE TABLE Events 
(
    ID INT IDENTITY(1,1) NOT NULL,
    Name NVARCHAR(64) NOT NULL,
    DefaultLocationID INT NULL,
    PRIMARY KEY(ID)
)

CREATE TABLE Locations
(
    ID INT IDENTITY(1,1) NOT NULL,
    EventID INT NOT NULL,
    Name NVARCHAR(64) NOT NULL,
    PRIMARY KEY(ID)
)

ALTER TABLE Events ADD FOREIGN KEY(DefaultLocationID) 
    REFERENCES Locations(ID) 
    ON UPDATE CASCADE ON DELETE SET NULL

ALTER TABLE Locations ADD FOREIGN KEY(EventID) 
    REFERENCES Events(ID) 
    ON UPDATE CASCADE ON DELETE CASCADE

My question is knowing that an Event can have a list of 0 or more Locations, and each Location can only be associated with exactly 1 Event.

I want the ability for an Event to have a default Location, but the integrity is that if an Event has a Location then that Locations foreign key must be the same Event.

My initial thought was to put a nullable foreign key in the Event table DefaultLocationID, but it dawned on me that there is no integrity to prevent that Location from being associated with a different Event.

My other idea was to have a IsDefault field on the Location table, but that doesn't stop multiple Locations from having their IsDefault field to true.

How would I enforce this relationship, preferabily without triggers and complex sub-queries?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

For your second option, you could put a filtered unique index on:

CREATE UNIQUE INDEX ix_DefaultLocation ON dbo.Locations(EventID, IsDefault)
WHERE IsDefault = 1

This will allow unlimited non-default locations, but only one default location per event id.

share|improve this answer
    
This looks like it will work exactly how I need, thank you! –  Matthew May 4 '12 at 15:32
3  
+1 but the following index will do it too: CREATE UNIQUE INDEX ix_DefaultLocation ON dbo.Locations(EventID) WHERE IsDefault = 1 –  A-K May 4 '12 at 17:48
1  
@AlexKuznetsov good point, the actual default field is unnecessary in the index since it's in the filter. –  JNK May 4 '12 at 17:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.