CODEWITHSUNDEEP
sqlsql-server
Philip Stratford
Philip Stratford

Reputation: 4753

Require Only One Of Multiple Columns Be Not Null

Apologies for the badly-worded title, but my inability to express exactly what I mean is probably why I can't find anything relating to this with Google.

Simply put, imagine a situation where you have table named Image in a database which holds pictures and related metadata. The photo could be of a client, of their car or of a particular repair being carried out on the car. There are tables called Client, Car and Repair, each with their own unique ID column.

In the Image table, there is a column named ClientID, a column named CarID and a column named RepairID, which links each image record to a client, car and/or repair. The problem is, an image might relate to a client AND a car but not a repair, or a car and repair but not a client, or only one of any of those three. In other words, there might be a value in one, two or three of the columns, but there MUST be a column in AT LEAST ONE. As long as one is not NULL, the other two can be.

Is there a way to achieve this with constraints (or any other method) in MSSQL?

Hopefully the example diagram below will illustrate what I'm talking about. enter image description here

Upvotes: 2

Views: 196

Answers (1)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171579

Create a constraint on the Image table that looks like this:

Car is not null or Client is not null or Repair is not null

DDL:

ALTER TABLE [dbo].[Image]  WITH CHECK ADD  CONSTRAINT [CK_Image_CarClientRepair] CHECK  (Car is not null or Client is not null or Repair is not null)

Upvotes: 3

Related Questions