No Check Foreign key constraints

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

SQL Server Administration (2005)

No Check Foreign key constraints

Author

Topic

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2009-03-19 : 13:37:42

Guys,

Is there any way for me to list all the foreign keys in the database which are marked as 'no check'.

ALTER TABLE [dbo].[EMPLOYEE] NOCHECK CONSTRAINT [FK_EMPLOYEE_JOB]

SYSOFOREIGNKEYS does not give that information.

Any suggestions or inputs would help.

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 14:12:38

have you had a look at INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS and INFORMATION_SCHEMA.TABLE_CONSTRAINTS views?

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2009-03-19 : 14:28:48

These system objects do not tell if the constraint is a check foreign key or nocheck foreign key.

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

Scenario 1

ALTER TABLE [dbo].[TABLE_A] WITH NOCHECK ADD CONSTRAINT [FK_TABLE_A_TABLE_B] FOREIGN KEY([TABLE_B_ID])
REFERENCES [dbo].[TABLE_B] ([ID])
GO
ALTER TABLE [dbo].[] CHECK CONSTRAINT [FK_TABLE_A_TABLE_B]

Scenario 2

ALTER TABLE [dbo].[TABLE_A] WITH NOCHECK ADD CONSTRAINT [FK_TABLE_A_TABLE_B] FOREIGN KEY([TABLE_B_ID])
REFERENCES [dbo].[TABLE_B] ([ID])
GO
ALTER TABLE [dbo].[] NOCHECK CONSTRAINT [FK_TABLE_A_TABLE_B]

I want to identify the occurrences of scenario 2 where the foreign key is marked as a check constraint.

Any suggestions and inputs would help

Thanks

amsqlguy
Yak Posting Veteran

89 Posts

Posted - 2009-03-19 : 16:07:22

This is how I was able to get it. I found this onsite

SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS [STATUS],
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
WHERE (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) = 'DISABLED'
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources