sysconstraints - SQL Server Forums

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

Transact-SQL (2000)

sysconstraints

Author

Topic

svicky9
Posting Yak Master

232 Posts

Posted - 2006-09-01 : 06:17:33

The Table structure for sysconstraints as per book online

constid int Constraint number.
id int ID of the table that owns the constraint.
colid smallint ID of the column on which the constraint is defined, 0 if a table constraint.
spare1 tinyint Reserved.
status int Bitmap indicating the status.

Possible values include:
1 = PRIMARY KEY constraint.
2 = UNIQUE KEY constraint.
3 = FOREIGN KEY constraint.
4 = CHECK constraint.
5 = DEFAULT constraint.
16 = Column-level constraint.
32 = Table-level constraint.

actions int Reserved.
error int Reserved.

---
But when i go to Query Analyser and type
select * from sysconstraints

i get the Column status as
2067
2068
2069

So why is it not showing it as 1,2,3

is it something to do with the Bitmap???

Vic

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-09-01 : 06:28:01

Yes, it is that way because it stores combination of values as a bitmap. If you want to check individual status bit, you have to do it like this...

select 
	case when status & 1 = 1 then 'primary key ' else '' end + 
	case when status & 2 = 2 then 'unique key ' else '' end + 
        .....
from sysconstraints

Harsh Athalye
India.
"Nothing is Impossible"

svicky9
Posting Yak Master

232 Posts

Posted - 2006-09-01 : 06:47:59

Thanks Harsh

if i do status & 3 = 3 it should print it as Foreign key ...But its displaying as Primary
If the Column is the Foreign Key of some other table and the primary key of the same table how does sql server distinguish it
Morever the Bitmap values show correctly distinguishing the primary and Foreign Key columns

I did the test on the Sample Northwind Database

Vic

Vic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-01 : 07:23:59

Maybe this binary explanation will help you


       2 1
       0 0 5 2 1
       4 2 1 5 2 6 3 1
       8 4 2 6 8 4 2 6 8 4 2 1

2067 - 1 0 0 0 0 0 0 1 0 0 1 1
2068 - 1 0 0 0 0 0 0 1 0 1 0 0
2069 - 1 0 0 0 0 0 0 1 0 1 0 1

       ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-01 : 07:37:39

Try this

SELECT		sc.constid ConstraintID,
		so.name TableName,
		CASE WHEN sc.colid = 0 THEN '' ELSE s.name END ColumnName,
		CASE
			WHEN sc.Status & 32 > 0 then 'Table-level'
			WHEN sc.Status & 16 > 0 then 'Column-level'
			WHEN sc.Status &  5 > 0 then 'DEFAULT'
			WHEN sc.Status &  4 > 0 then 'CHECK'
			WHEN sc.Status &  3 > 0 then 'FOREIGN KEY'
			WHEN sc.Status &  2 > 0 then 'UNIQUE KEY'
			WHEN sc.Status &  1 > 0 then 'PRIMARY KEY'
		END + ' constraint'
FROM		sysconstraints sc
INNER JOIN	sysobjects so ON so.id = sc.id AND so.xtype = 'u'
LEFT JOIN	syscolumns s ON s.colid = sc.colid AND so.id = s.id

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-01 : 07:39:42

quote:
Originally posted by svicky9

if i do status & 3 = 3 it should print it as Foreign key ...But its displaying as Primary

When checking for 3, the two rightmost bits are set, bit 1 and bit 2. So you have to be careful in which order you test them.

Peter Larsson
Helsingborg, Sweden

svicky9
Posting Yak Master

232 Posts

Posted - 2006-09-01 : 07:50:49

thanks Peso

But it shows only the Table level and Column level Constraints nothing else

Vic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-01 : 07:55:36

Slightly revised

SELECT		sc.constid ConstraintID,
		so.name TableName,
		CASE WHEN sc.colid = 0 THEN '' ELSE s.name END ColumnName,
		SUBSTRING(
				CASE WHEN sc.Status & 32 = 32 THEN ', Table-level' ELSE '' END +
				CASE WHEN sc.Status & 16 = 16 then ', Column-level' ELSE '' END +
				CASE WHEN sc.Status &  5 = 5 then ', DEFAULT' ELSE '' END +
				CASE WHEN sc.Status &  4 = 4 then ', CHECK' ELSE '' END +
				CASE WHEN sc.Status &  3 = 3 then ', FOREIGN KEY' ELSE '' END +
				CASE WHEN sc.Status &  2 = 2 then ', UNIQUE KEY' ELSE '' END +
				CASE WHEN sc.Status &  1 = 1 then ', PRIMARY KEY' ELSE '' END,
			 3, 100) + ' constraint'
FROM		sysconstraints sc
INNER JOIN	sysobjects so ON so.id = sc.id AND so.xtype = 'u'
LEFT JOIN	syscolumns s ON s.colid = sc.colid AND so.id = s.id

Peter Larsson
Helsingborg, Sweden

svicky9
Posting Yak Master

232 Posts

Posted - 2006-09-01 : 08:43:59

well thats worked ...thanks PESO

Vic

hacene
Starting Member

2 Posts

Posted - 2012-09-27 : 07:02:52

so whene we have a foreign kay this script will show it as "forign key + unique key + primary key"
because filter for froreigne key is 3 : 1 1
filter for unique key is 2 : 1 0
filter for primary key is 1 : 0 1

hacene
Starting Member

2 Posts

Posted - 2012-09-27 : 07:13:17

I mean that when we have a foreign key it could not be parimary or unique key

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources