Missing Foreign Key Constraints

Question

Missing Foreign Key Constraints

Pappo

SSC-Addicted

Points: 473
More actions
December 2, 2005 at 9:15 am

#110953

How can I use the system tables to find missing foreign key constraints?

Assuming these are my table structures:
primary key column     foreign key column
address.location_id     tbl2.location_id
address.location_id      tbl3.location_id

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

I don't think the system tables are going to help you here if the foreign key constraints were never specified. You could try and do a column_name to column_name comparison (and check the column type), but that's not a guarantee you're going to get everything. It also could mean a lot of false matches. Typically this sort of thing gets defined in a design document and from there you can investigate whether said foreign keys are present. Do you have such a design document or similar notes?

K. Brian Kelley
@kbriankelley

Pappo SSC-Addicted Points: 473 More actions · Answer 2

Pappo

SSC-Addicted

Points: 473

December 2, 2005 at 12:56 pm

#607490

No i don't.

Pappo SSC-Addicted Points: 473 More actions · Answer 3

Brian,

Believe it or not this is a pre-interview question.

Here's the full question:

Write a SQL script for Microsoft SQL 2000 or SQL 2005 that uses the system tables to find the missing foreign key constraints. Assume a database with:

· 1000 user tables

· All user tables have a primary key as a single integer column

· All primary key column names are unique within the database

· All user tables with foreign keys have column names that either match the name of the related table primary key column name exactly or as the suffix;

Then the table structure above.

sushila SSC-Dedicated Points: 35293 More actions · Answer 4

Pappo - until someone (Brian ?!?!) comes up with something less convoluted, you could use something like this...of course, the assumption is that all the "key" names are going to be the SQL Server default ones...

SELECT A.COL, A.TBL FROM 
(SELECT A.COLUMN_NAME COL, A.TABLE_NAME TBL FROM INFORMATION_SCHEMA.COLUMNS A, INFORMATION_SCHEMA.KEY_COLUMN_USAGE B
WHERE A.TABLE_NAME  B.TABLE_NAME AND
B.CONSTRAINT_NAME LIKE 'PK_%' AND
A.COLUMN_NAME LIKE '%' + B.COLUMN_NAME  AND B.TABLE_NAME NOT LIKE 'dt%')A 
WHERE NOT EXISTS
(SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
WHERE A.COL = COLUMN_NAME AND A.TBL = TABLE_NAME AND CONSTRAINT_NAME LIKE 'FK_%')

**ASCII stupid question, get a stupid ANSI !!!**