CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Oleg Sh
Oleg Sh

Reputation: 9013

SQL Rules depend on selected value

I have the following simple DB:

Table Types:
- ID int
- TypeName nvarchar

Table Users:
- ID int
- UserName nvarchar
- TypeID int

Table BusyTime
- ID int
- UserID int
- BTime time(0)

But one restriction - records in BusyTime should be only for users, which have TypeID = 3. Users with TypeID = 1 and with TypeID = 2 can't have records in BusyTime (it contradicts the business logic) How to describe it on MS SQL level? Or should I redesign DB ?

Upvotes: 0

Views: 62

Answers (3)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

I'm assuming your primary keys in each table are just on ID. What you need to change is, add a UNIQUE KEY constraint on both ID and TypeID in Users:

ALTER TABLE Users ADD CONSTRAINT UQ_User_Types_XRef (ID,TypeID)

And create the BusyTime table as:

CREATE TABLE BusyTime (
   ID int not null,
   UserID int not null,
   BTime time(0) not null,
   _Type_XRef as 3 persisted,
   constraint PK_BusyTime PRIMARY KEY (ID),
   constraint FK_BusyTime_Users FOREIGN KEY (UserID)
        references Users (ID),
   constraint FK_BusyTime_Users_XRef FOREIGN KEY (UserID,_Type_XRef) 
        references Users (ID,TypeID)
)

Where I've assumed PK_BusyTime and FK_BusyTime_Users were your existing constraints. It's a matter of taste whether you drop FK_BusyTime_Users (which is the "real" foreign key constraint) now that FK_BusyTime_Users_XRef exists.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Setting up such a conditional constraint is possible. One method is to add compound index to users:

create unique index idx_users(type, id) on users(type, id)

Then use this for a foreign key constraint:

alter table busytime add _type as (3) persisted not null;
alter table busytime add constraint foreign key (_type, userId) on users(type, id);

Unfortunately, I think the persisted is needed for this column so it actually occupies space in the record.

Unfortunately, I don't think this works either:

alter table busytime add constraint foreign key (3, userId) on users(type, id);

Upvotes: 0

Lee
Lee

Reputation: 584

You can use check constraints to disallow invalid types: https://technet.microsoft.com/en-us/library/ms188258%28v=sql.105%29.aspx

Upvotes: 0

Related Questions