TABLE CONSTRAINT Issue

Question

Post reply

TABLE CONSTRAINT Issue

mar.ko

Default port

Points: 1451
More actions
November 5, 2015 at 9:27 am

#322127

Is it possible to create a constraint that specifies that if one column is null, the other column cannot be null and vice versa ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Yes it is.

It'll be a CHECK constraint.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mar.ko Default port Points: 1451 More actions · Answer 2

Thanks, wasn't sure if the following syntax was supported or not....

CONSTRAINT C_KEYS CHECK (CTRL_ID IS NOT NULL OR CTRL_KEY IS NOT NULL),

mar.ko Default port Points: 1451 More actions · Answer 3

Now I have a new problem.

I want my table to have two "keys", one integer, one string so that a value can be accessed by either.

I have solved the problem of insuring one of these columns is not null,

but how do I insure that the key entered is unique ?

In other words, how can I specify a constraint that insures either column has unique values, yet nulls are allowed ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

For that you either need a unique index on a computed column (A Coalesce of the two would work in this case), or a filtered index.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mar.ko Default port Points: 1451 More actions · Answer 5

Thanks Gail -

I think that computed column with unique index is the best approach.

From what I've gathered, filtered indexes can only be created with filters on a single column.

MadAdmin SSChampion Points: 11261 More actions · Answer 6

You would create 2 contraints, one on each column to ensure that the not null values have a 1 to 1 relationship to each other.

2 unique constraints would be best, but since you need nullible values, as Gail has already suggested, unique index with filter would probably be the only way to go.

Don't make the mistake of creating a PK on (column1,column2), check that error by insert(1,1), then insert(1,2)

Example below.

create table Mytable (Column1 int, Column2 varchar(20) null,primary key clustered (Column1))

go

Create unique nonclustered index IX_Column2 on Mytable(Column2) where column2 is not null

Catch-all queries done right [/url]
Gail Shaw's Performance Blog[/url]

mar.ko Default port Points: 1451 More actions · Answer 7

Gail - I ended-up using a constraint instead of a unique index.

CREATE TABLE [dbo].[CONTROL_FACT](

[ID] [int] IDENTITY(1,1) NOT NULL,

[CTRL_ID] [int] NULL, -- 1

[CTRL_KEY] [varchar](50) NULL, -- 'TARGET_LAST_UPDATE'

[CTRL_VALUE] [varchar](1000) NULL, -- '2015-11-03', 'abc@xxxxyyyy.com'

[CTRL_VERS] varchar(1) NULL, -- P=Prod Q=QA D=Dev, etc

[CTRL_DATATYPE] varchar(30) NULL, -- DECIMAL(9,6), int, smalldatetime, etc. Default = varchar

[ID_KEY_UNIQUE] AS COALESCE([CTRL_ID],[CTRL_KEY]), -- computed as we want a unique id set and unique key set

CONSTRAINT C_KEYS CHECK (CTRL_ID IS NOT NULL OR CTRL_KEY IS NOT NULL), -- one or the other key must be supplied

CONSTRAINT U_CTRLID_KEY UNIQUE(ID_KEY_UNIQUE),

CONSTRAINT [PK_CTRL] PRIMARY KEY CLUSTERED

(

ID

),

) ON [PRIMARY]