CODEWITHSUNDEEP
sql-server
lenmez
lenmez

Reputation: 5

Creating constraint based on a value from referenced table in SQL Server

I have two tables:

Pencils:

ID(PK): 0,1,2,3

Pencil: Type1, Type2, Type3, Type4

Color_ID(FK, references Colors(ID)): 0,0,0,0

Colors:

ID(PK):0,1,2

Color:Red, Blue, Green

Availability:Yes, No, No

I want to create constraint in the Pencil table so that the user cannot enter the Color_ID value of a color whose availability is No. What's the best way to do this ? Thanks.

Upvotes: 0

Views: 47

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

You can add a superkey to the color table that includes the availability information, and then use a foreign key with a computed column to ensure that it's constrained:

create table Colors (
    ID int not null,
    Name varchar(30) not null,
    Available bit not null,
    constraint PK_Colors PRIMARY KEY (ID),
    constraint UQ_Color_Names UNIQUE (Name),
    constraint UQ_Color_AvailabilityXRef UNIQUE (ID,Available)
)
go
create table Pencils (
    ID int not null,
    Pencil varchar(20) not null,
    ColorID int not null,
    _Available as CAST(1 as bit) persisted,
    constraint PK_Pencils PRIMARY KEY (ID),
    constraint FK_Pencil_Colors FOREIGN KEY (ColorID) references Colors (ID),
    constraint FK_PenciL_Color_AvailabilityXRef FOREIGN KEY (ColorID,_Available) 
                                                      references Colors (ID,Available)
)

You'll note that strictly speaking, FK_Pencil_Colors is now redundant. I prefer to retain it since it's the "real" FK but others may choose to drop it.

Note also that this prevents a color from being made unavailable whilst there are still pencils referencing it, an added constraint bonus here.1

Finally, it's just my convention that I prefix names with _ when they're not intended to be used by "other people". They're there to help me build the DRI.

1As Jeroen comments, this may not actually be your desired behaviour. Bear in mind that generally, constraints state "eternal truths". If it's a constraint, then it should be possible to delete and re-insert your data without violating constraints. If you have a temporal requirement, such as "X and Y should be true at the point in time at which the row is inserted" then it's usually best to deal with this using triggers.

Upvotes: 1

Related Questions