2

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

Improve this question

1 Answer 1

Reset to default
3

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)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

That looks simple enough! Are you aware of any particular performance issues this might create?
There would be virtually no overhead with this, it is a trivial check.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.