how to check if unique key constraint exists in sql server?

Remu

36 Points

1 Posts

I tried following SQL command but not one working:

IF(NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS   WHERE CONSTRAINT_NAME ='UQ_TableName_TypeId'))
BEGIN
  CREATE UNIQUE NONCLUSTERED INDEX UQ_TableName_TypeId ON [dbo].[TableName]([TypeId] ASC) WHERE [TypeId] IS NOT NULL
END

Tried with:

TABLE_CONSTRAINTS
REFERENTIAL_CONSTRAINTS
CHECK_CONSTRAINTS

sql-server tsql

Views: 508
Total Answered: 3
Total Marked As Answer: 2
Posted On: 07-Apr-2024 22:28

Share:

Answers

Rashmi 1068 Points 19 Posts	Try follwoing `IF OBJECT_ID('dbo.[UQ_TableName_TypeId]', 'UQ') IS NOT NULL BEGIN CREATE UNIQUE NONCLUSTERED INDEX UQ_TableName_TypeId ON [dbo].[TableName]([TypeId] ASC) WHERE [TypeId] IS NOT NULL END` It's easily doable. Posted On: 07-Apr-2024 22:33 Great! - Raj 08-Apr-2024 04:19 not working. - dev3 22-Apr-2024 23:29
Remu 36 Points 1 Posts	Thanks for quic reponse. Work for me. Posted On: 07-Apr-2024 22:38
dev3 154 Points 3 Posts	To me instead of above, following works: `IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = N'UQ_TableName_TypeId') BEGIN CREATE UNIQUE NONCLUSTERED INDEX UQ_TableName_TypeId ON [dbo].[TableName]([TypeId] ASC) WHERE [TypeId] IS NOT NULL END` Posted On: 22-Apr-2024 23:29 Thanks for sharing solution. - Brian 23-Apr-2024 22:02