CODEWITHSUNDEEP
sql-serversql-server-2008database-designconstraints
fearofawhackplanet
fearofawhackplanet

Reputation: 53396

SqlServer constrain field value dependent on another field value

As the question title, is there a way to do this?

eg:

DrivingLicence
--------------- 
CanDriveCar       (bool) 
CanDriveMotorbike (bool) 
CanDriveBus       (bool)

You can't drive a bus without a car licence, so I want the DB to throw an exception if anyone tries to give someone a bus licence when they haven't a car licence.

Upvotes: 1

Views: 290

Answers (2)

Pondlife
Pondlife

Reputation: 16240

Martin gave you a possible answer, but it's unclear from your post what your data model is: do a car licence and a bus licence have the same licence number or not? Put another way, if I have a car licence and learn to drive a bus, do I get a second licence or just a stamp on my existing licence?

If they are separate licenses, then I assume they will be separate rows in your table because the primary key of the table is - hopefully! - LicenceNumber. And in that case CHECK constraints will not work because they only apply to values in one row; you would need to use triggers to check what licenses a driver already has when you add a new one.

So your options are:

  1. Drivers can only ever have one licence with one unique number: use a CHECK constraint
  2. Drivers can have multiple licences of different types with different numbers: use a trigger
  3. It's more complicated than that: use a stored proc or application code for inserting new licences

Your post suggests 1, but it's a bit unclear. And even if that is the case, you might consider a second table called DrivingLicenceQualifications or whatever: if one licence can have many qualifications, and if you expect to add new qualifications in future, then that would be more flexible.

And FYI, there is no Boolean data type in MSSQL, perhaps you meant BIT?

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453278

ALTER TABLE dbo.DrivingLicence ADD CONSTRAINT
    NameOfConstraint CHECK (CanDriveBus=0 OR CanDriveCar=1)

Upvotes: 2

Related Questions