How to add a constraint - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

How to add a constraint

Author

Topic

reddy_vam
Starting Member

43 Posts

Posted - 2008-11-26 : 09:49:35

Hi Folks,

I have a table which is having 4 columns and in my scenario upon
update of column3 = yes or no column4 should not be blank, Can i able to add any check constraint on this?

Thanks in advance,
Vamshi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-26 : 10:21:40

[code]ALTER TABLE Table1 WITH NOCHECK
ADD CONSTRAINT CK_MyCheck
CHECK ( Column3 IN ('Yes', 'No') AND COALESCE(Column4, '') > '' OR Column3 NOT IN ('Yes', 'No') )

ALTER TABLE Table CHECK CONSTRAINT CK_MyCheck[/code]

E 12°55'05.63"
N 56°04'39.26"

reddy_vam
Starting Member

43 Posts

Posted - 2008-11-26 : 10:52:51

Hi Peso,

It's not working, i tried with the above command after that i inserted a single value 'Yes' into Column3.It was executed without showing any errors.

My question is if we are parsing 'Yes' or 'NO' into Column3, we should not left blank in Column4

Thanks,
Vamshi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-26 : 12:08:08

then add a trigger


CREATE TRIGGER YourTrigger
ON Table1 
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (SELECT 1
           FROM INSERTED
           WHERE Col3 ='yes' OR Col3 ='No'
           AND Col4 ='')
 RAISERROR 'Col4 cant be blank when col3 has yes/no value',10,1
ELSE
INSERT INTO Table1
SELECT * FROM INSERTED
END

reddy_vam
Starting Member

43 Posts

Posted - 2008-11-26 : 15:14:44

Hi Visakh,

I tried with the trigger, it was working for only 2 conditions i.e CONDITION 1 & 3 , and failing for 2 conditions i.e CONDITION 2 & 4, below are they

--CONDITION 1 ( Correct)
insert into table(Col3) values ('Y')
Col4 Field cannot be blank when Col3 has Y/N value

(1 row(s) affected)

--CONDITION 2 ( Wrong )
insert table(Col3,Col4) values ('Y','Success')
Col4 Field cannot be blank when Col3 has Y/N value

(1 row(s) affected)
--CONDITION 3 ( Correct )
insert table(Col3,Col4) values ('N','Fail')
(1 row(s) affected)

(1 row(s) affected)

--CONDITION 4 ( Wrong )
insert table(Col3) values ('N')

(1 row(s) affected)

(1 row(s) affected)

Thanks,
Vamshi

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-26 : 15:33:30

quote:
Originally posted by reddy_vam

Hi Visakh,

I tried with the trigger, it was working for only 2 conditions i.e CONDITION 1 & 3 , and failing for 2 conditions i.e CONDITION 2 & 4, below are they

--CONDITION 1 ( Correct)
insert into table(Col3) values ('Y')
Col4 Field cannot be blank when Col3 has Y/N value

(1 row(s) affected)

--CONDITION 2 ( Wrong )
insert table(Col3,Col4) values ('Y','Success')
Col4 Field cannot be blank when Col3 has Y/N value

(1 row(s) affected)
--CONDITION 3 ( Correct )
insert table(Col3,Col4) values ('N','Fail')
(1 row(s) affected)

(1 row(s) affected)

--CONDITION 4 ( Wrong )
insert table(Col3) values ('N')

(1 row(s) affected)

(1 row(s) affected)

Thanks,
Vamshi

Its working for me:

Visakh's code:

CREATE TRIGGER YourTrigger
ON Table1
INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (SELECT 1
FROM INSERTED
WHERE (Col3 ='yes' OR Col3 ='No')
AND Col4 ='')
RAISERROR ('Col4 cant be blank when col3 has yes/no value',10,1)
ELSE
INSERT INTO Table1
SELECT * FROM INSERTED
END

reddy_vam
Starting Member

43 Posts

Posted - 2008-11-26 : 16:01:05

Sodeep,

It is working when you explicitly passing Col3 and Col4,
but if you are not passing Col4 in the Insert statement it is not working

In my scenario i am passing only Col3 value (Condition 4) like shown below

insert into table1(Col3) values ('N')
so that even though i am not passing Col4 value it was taking it as NULL after inserting into the table which was wrong.

Thanks,
Vamshi

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-26 : 16:18:49

Then you should make '' as default value for col4 so it triggers and doesn't let you insert.

reddy_vam
Starting Member

43 Posts

Posted - 2008-11-26 : 16:40:07

Sodeep/Visakh,

Thats Perfect Sodeep, its working now & thanks alot to Visakh for sending the code.

Thanks,
Vamshi

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-26 : 16:49:14

Welcome !!! Happy Thanksgiving