Table Contstraints - 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)

Table Contstraints

Author

Topic

nduggan23
Starting Member

42 Posts

Posted - 2007-11-12 : 05:58:08

Hi all,

Quick question, is it possible to implement a constraint over two columns that specifies that one column must contain a value and not the other.

ie


create table x
( id int NOT NULL,
 column1 int null,
 column2 int null,
 constraint Y (??)
);

insert into x(1, 1, NULL) -- this should work under the constraint
insert into x(1, NULL, 1) -- this should work under the constraint
insert into x(1, 1, 1)  -- this should fail under the constraint
insert into x(1, NULL, NULL) -- this should fail under the constraint

I know the sql isnt 100% accurate. Just looking to see if its possible to do what i want?

Regards

Niall.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-12 : 06:33:04

[code]declare @x table
(
id int NOT NULL,
column1 int null,
column2 int null,
check ((column1 is not null and column2 is null) or (column1 is null and column2 is not null))
)

insert into @x values(1, 1, NULL) -- this should work under the constraint
insert into @x values(1, NULL, 1) -- this should work under the constraint
insert into @x values(1, 1, 1) -- this should fail under the constraint
insert into @x values(1, NULL, NULL) -- this should fail under the constraint[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nduggan23
Starting Member

42 Posts

Posted - 2007-11-12 : 06:35:46

Cheers harsh_athalye

Also, is this good or bad practice? :) Should i just handle it in the code?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-12 : 06:39:42

It depends. If your table gets data only from the SP, then its good to put the check in the code. But if there are multiple ways in which the table can be feeded, it is best to keep check at table level to ensure that criteria will be fulfiled.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nduggan23
Starting Member

42 Posts

Posted - 2007-11-12 : 06:41:42

Cheers, in theory the data should only be entered via a SP. but i wont be maintaining this system long term..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources