Add CHECK Constraint to Avoid all Three Columns are Equal




CREATE TABLE [dbo].[test1](
	[cola] [int] not NULL,
	[colb] [int] not NULL,
	[colc] [int] not NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[test1]  WITH CHECK ADD  CONSTRAINT [CheckNotAllColsEqual] CHECK  (([cola]=[colb] AND [colb]<>[colc] OR [colb]=[colc] AND [cola]<>[colb] OR [cola]=[Colc] AND [colb]<>[Colc] OR [cola]<>[Colb] AND [Cola]<>[colc]))
GO

ALTER TABLE [dbo].[test1] CHECK CONSTRAINT [CheckNotAllColsEqual]
GO



 

Here is a very simple way to do the same from Scott:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/290ca797-c9d6-4f3c-9915-c7b86d688f6d/candidate-key?forum=transactsql#42fd7f0b-a6d2-4eb2-9a20-5580a2b4ab35




ALTER TABLE [dbo].[test1]  WITH CHECK ADD constraint ckx check (not(cola = colb and colb = colc))
GO



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s