Finding unique constraints through select

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 2008 Forums

SQL Server Administration (2008)

Finding unique constraints through select

Author

Topic

bmahf
Starting Member

14 Posts

Posted - 2011-08-22 : 17:32:32

I'm trying to use a select to find all unique constraints for a table. I can easily find the primary and foreign key constraints and tie them to the respective columns, but I can only see unique constraints defined in sys.indexes and referenced in INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS. Neither seem to give any meaningful information about which column(s) the unique constraint was created on. Can anyone give me a hint on this?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-22 : 17:42:05

Here's one way:


select c.constraint_name, col.column_name 
from   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE col
join   INFORMATION_SCHEMA.TABLE_CONSTRAINTS c on c.constraint_name = col.constraint_name
where  c.table_name = '<yourTable>'
and    c.constraint_type = 'unique'
order by constraint_name

Be One with the Optimizer
TG

bmahf
Starting Member

14 Posts

Posted - 2011-08-22 : 17:56:18

I have got to be missing something here. I have a script that was used to create all objects in my database, and it specifies that an index is being created, with the name UQ_<something>. When I ran your select, I get nothing, so I looked at the full content of INFORMATION_SCHEMA.TABLE_CONSTRAINTS and saw that only FOREIGN KEY and PRIMARY KEY are there. My create script does this:

--------------SQL SCRIPT-------------------------
CREATE TABLE [dbo].[targetgroup] (
[GrpIndex] int identity NOT NULL
, [GroupID] int NOT NULL
, [ClassID] int NOT NULL
, [ExerciseId] int NOT NULL
, [EffectiveDate] datetime NOT NULL
)
GO

ALTER TABLE [dbo].[targetgroup] ADD CONSTRAINT [PK__targroup__2FAC98AB3CF40B7E] PRIMARY KEY CLUSTERED (
[GrpIndex]
)
GO

CREATE INDEX [UQ__targroup__149AF30B3FD07829] ON [dbo].[targetgroup] (
[GroupID]
, [ClassID]
, [ExerciseId]
)
GO
---------------------------------

But there is no index named UQ__targroup__149AF30B3FD07829 in either
INFORMATION_SCHEMA.TABLE_CONSTRAINTS or INFORMATION_SCHEMA.TABLE_CONSTRAINTS.

Confused...

bmahf
Starting Member

14 Posts

Posted - 2011-08-22 : 17:58:29

Oops, I meant to say I couldn't find it in either the INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE or INFORMATION_SCHEMA.TABLE_CONSTRAINTS views.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-22 : 18:24:46

You have a primary key (clustered) named this: PK__targroup__2FAC98AB3CF40B7E
and you have an index named this: UQ__targroup__149AF30B3FD07829

That index is not defined as a unique constraint. It is just an index and does not enforce uniqueness.

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-08-22 : 18:27:20

[code]
alter table targetgroup add constraint UQ__targroup_GroupID_ClassID_ExerciseID
unique ([GroupID], [ClassID], [ExerciseId])
go
/*
CREATE INDEX [UQ__targroup__149AF30B3FD07829] ON [dbo].[targetgroup] (
[GroupID]
, [ClassID]
, [ExerciseId]
)

*/
[/code]

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources