CODEWITHSUNDEEP
sql-server-2005
Fredou
Fredou

Reputation: 20100

with 2 field that allow null how to make a constrain that at only one must be filled?

to simplify this let take that table:

 table1
 -------------
 id unique primary int
 myVal1 int null (fk)
 myVal2 int null (fk)
 myData int not null

what would be the best way to create a constrain on this table so only one value can be filled?

these would work:

insert into table1 (myval1,myData) values (1,234)
insert into table1 (myval2,myData) values (1,123)

these would not work:

insert into table1 (myData) values (234)
insert into table1 (myVal1,myval2,myData) values (1,2,123)

Upvotes: 1

Views: 53

Answers (1)

KM.
KM.

Reputation: 103589

try using a check constraint:

CREATE TABLE dbo.Table1
    (
    rowID int NOT NULL primary key identity(1,1),
    myVal1 int NULL,
    myVal2 int NULL,
    myData int NOT NULL
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Table1 ADD CONSTRAINT
    CK_Table1_myVal1_or_myVal2 CHECK ((myVal2 IS NOT NULL AND myVal1 IS NULL) OR (myVal2 IS NULL AND myVal1 IS NOT NULL))
GO

Upvotes: 1

Related Questions