Problem with conditional column constraints

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem with conditional column constraints

Author

Topic

Generaltao
Starting Member

8 Posts

Posted - 2004-09-13 : 08:09:32

Wow, that title has some serious aliteration going on.

I was wondering if you fine folks could help me with a little problem I'm having. I'm learning SQL Server 2000 and started my first project. The project revolves around a car leasing company. One of the constraints is for the vehicles, where "vehicles cannot have power locks if they do not have air conditioning."

Now, I made both 'AirConditioning' and 'Locks' as boolean fields (bit) but I have no clue how to implement a constraint to check for a condition :(.

Keep in mind that the table and columns were already created, and just need to have constraints added to them afterwards. The extent of my knowledge at the moment is exemplified with the following:

quote:
ALTER TABLE dbo.Lease ADD CONSTRAINT
CK_First_Payment CHECK (([FirstPaymentDate] > [ContractDate]))
GO

If you guys could help me out I would really appreciate it. Perhaps when I get really good I could help out the beginners as well, but for now I should be lumped in the "needy" category :(.

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-13 : 08:14:00

Booleans are more like 0 and 1 instead of true and false. For example

IF MyBooleanColumn BEGIN

Doesn't compile. But

IF MyBooleanColumn = 1 BEGIN

Works fine

Being constraint challenged myself, I'd try something like

ALTER TABLE dbo.Lease ADD CONSTRAINT
CK_Locks CHECK (([AirConditioning] = [Locks]))
GO

What error do you get?

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-13 : 08:16:27

Um. Now I realize you could have Air Conditioning and no locks. A "Conditional" constraint.

Generaltao
Starting Member

8 Posts

Posted - 2004-09-13 : 08:22:47

quote:
Originally posted by SamC

Um. Now I realize you could have Air Conditioning and no locks. A "Conditional" constraint.

Precisely. Not really sure how to deal with that. The logic would have to be:

IF AirConditioning = True
Locks = True
Else
Locks = False
--Print error message

However, I have no clue how to do that, as I wasn't really shown in the book for the course I'm taking.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-09-13 : 08:26:32

ALTER TABLE dbo.Lease ADD CONSTRAINT CK_Locks_AC CHECK ((AirConditioning=1 OR PowerLocks=0) OR (AirConditioning=0 AND PowerLocks=0))

That should do it.

Generaltao
Starting Member

8 Posts

Posted - 2004-09-13 : 08:38:35

Holy moly...

Thank you kindly!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-13 : 09:06:17

That's close, what if both are 1? I believe that is a valid combo but that will fail the check constraint suggested.

If I am reading this correctly, the logic required is:

PowerLocks implies AC

Which means:

if PowerLocks is true, then AC must be true. If PowerLocks is false, it doesn't matter what AC is.

That is expressed as:

(Not PowerLocks) OR (AC)

So your check constraint would be:

(PowerLocks=0) or (AirConditioning= 1)

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-09-13 : 09:52:40

quote:
if PowerLocks is true, then AC must be true. If PowerLocks is false, it doesn't matter what AC is

That's exactly what the first clause does, only it's also saying that as long as AC is true, it doesn't matter what PowerLocks is.

In fact, I think that only the first part of the constraint is needed, might be worth testing:

ALTER TABLE dbo.Lease ADD CONSTRAINT CK_Locks_AC CHECK (AirConditioning=1 OR PowerLocks=0)

Generaltao
Starting Member

8 Posts

Posted - 2004-09-13 : 10:57:11

Thanks a lot for your help guys. It helped to lay the ground work for that particular solution, and I now know how to solve that sort of problem in the future!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-13 : 11:32:00

Rob you posted the same expression as mine just backwards !

But surely you can see your first solution did not allow for both to equal 1. Work it out on a truth table if you're not sure.

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-09-13 : 11:44:05

Sure it does. Both the long version and short version allow for both to equal zero or one. It only prevents the one condition (AC=0, PL=1) that is forbidden. The second clause is redundant, and only explicitly describes the condition where both are zero. Even if it failed, the first clause would allow the correct logic.

I just tried both versions on tables and they worked the same.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-13 : 12:30:15

just noticed you had an OR connecting the two conditions in your first one, not an AND which I thought it was ... so you are correct, sir: the first does allow for 1,1. but of course the shorter version is less redundant and the best solution.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-13 : 12:30:34

Yup


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myLease99(AirConditioning bit, PowerLocks bit)
GO

ALTER TABLE dbo.myLease99 
	ADD CONSTRAINT CK_Locks_AC 
		CHECK (
			(AirConditioning=1 OR  PowerLocks=0) 
--		     OR (AirConditioning=0 AND PowerLocks=0)
)
GO

DECLARE @x bit, @y bit
SELECT @x = 1, @y = 1
INSERT INTO myLease99(AirConditioning, PowerLocks)
SELECT @x, @y
SELECT 'INSERT ' + CONVERT(char(1),@x)+ ', ' + CONVERT(char(1),@y) + ' -  ERROR: ' + CONVERT(varchar(3),@@ERROR)

SELECT @x = 1, @y = 0
INSERT INTO myLease99(AirConditioning, PowerLocks)
SELECT @x, @y
SELECT 'INSERT ' + CONVERT(char(1),@x)+ ', ' + CONVERT(char(1),@y) + ' -  ERROR: ' + CONVERT(varchar(3),@@ERROR)

SELECT @x = 0, @y = 1
INSERT INTO myLease99(AirConditioning, PowerLocks)
SELECT @x, @y
SELECT 'INSERT ' + CONVERT(char(1),@x)+ ', ' + CONVERT(char(1),@y) + ' -  ERROR: ' + CONVERT(varchar(3),@@ERROR)

SELECT @x = 0, @y = 0
INSERT INTO myLease99(AirConditioning, PowerLocks)
SELECT @x, @y
SELECT 'INSERT ' + CONVERT(char(1),@x)+ ', ' + CONVERT(char(1),@y) + ' -  ERROR: ' + CONVERT(varchar(3),@@ERROR)

SELECT @x = 0, @y = NULL
INSERT INTO myLease99(AirConditioning, PowerLocks)
SELECT @x, @y
SELECT 'INSERT ' + ISNULL(CONVERT(char(1),@x),'NULL') + ', ' +  ISNULL(CONVERT(char(1),@y),'NULL')  + ' -  ERROR: ' + CONVERT(varchar(3),@@ERROR)

SELECT @x = NULL, @y = 0
INSERT INTO myLease99(AirConditioning, PowerLocks)
SELECT @x, @y
SELECT 'INSERT ' + ISNULL(CONVERT(char(1),@x),'NULL') + ', ' +  ISNULL(CONVERT(char(1),@y),'NULL')  + ' -  ERROR: ' + CONVERT(varchar(3),@@ERROR)

SELECT @x = 1, @y = NULL
INSERT INTO myLease99(AirConditioning, PowerLocks)
SELECT @x, @y
SELECT 'INSERT ' + ISNULL(CONVERT(char(1),@x),'NULL') + ', ' +  ISNULL(CONVERT(char(1),@y),'NULL')  + ' -  ERROR: ' + CONVERT(varchar(3),@@ERROR)

SELECT @x = NULL, @y = 1
INSERT INTO myLease99(AirConditioning, PowerLocks)
SELECT @x, @y
SELECT 'INSERT ' + ISNULL(CONVERT(char(1),@x),'NULL') + ', ' +  ISNULL(CONVERT(char(1),@y),'NULL')  + ' -  ERROR: ' + CONVERT(varchar(3),@@ERROR)
GO

SET NOCOUNT OFF
DROP TABLE myLease99
GO

I love redundant margaritas....

Brett

8-)

SamC
White Water Yakist

3467 Posts

Posted - 2004-09-13 : 15:38:26

ALTER TABLE dbo.Lease ADD CONSTRAINT CK_Locks_AC CHECK (NOT (AirConditioning=0 AND PowerLocks=1))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources