Sql-server – Relationship with a default value

referential-integritysql servert-sql

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?

Best Answer

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.

Related Question