Complicated Contraints - 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 2008 Forums

SQL Server Administration (2008)

Complicated Contraints

Author

Topic

whmueller
Starting Member

2 Posts

Posted - 2011-06-08 : 14:36:58

I have 4 null-able int (ID) fields in a new table and want to create a constraint where: if the 4th field is not null, the 3rd field cannot be null and either the 1st or 2nd fields must not be null. Any idea how I might write that as a "Check Constraint Expression"?

Thanks In Advance,
William

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-08 : 14:53:08

[code]create table #a(i1 int null, i2 int null, i3 int null, i4 int null,
constraint chk check(i4 is null or (i3 is not null and coalesce(i1,i2) is not null)))

insert #a(i1,i2,i3,i4) values(1,null,null,null)
insert #a(i1,i2,i3,i4) values(null,1,null,null)
insert #a(i1,i2,i3,i4) values(null,null,1,null)
insert #a(i1,i2,i3,i4) values(null,null,null,1) -- fails
insert #a(i1,i2,i3,i4) values(null,null,1,1) -- fails
insert #a(i1,i2,i3,i4) values(null,1,null,1) -- fails
insert #a(i1,i2,i3,i4) values(1,1,null,1) -- fails
insert #a(i1,i2,i3,i4) values(1,null,1,1)
insert #a(i1,i2,i3,i4) values(null,1,1,1)
insert #a(i1,i2,i3,i4) values(1,1,1,1)[/code]

whmueller
Starting Member

2 Posts

Posted - 2011-06-08 : 15:00:15

Awesome! tyvm

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources