none
Foreign Keys in SQL 2005

    Question

  • How to identify all missing Foreign Keys in sql 2005.

    Today i written a query to list of all the foreign keys in a Database. But only i got less number of keys than what i expected.

     

    please help me.

    Tuesday, August 23, 2011 9:34 PM

All replies

  • Check

    Missing foreign key constraints


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, August 23, 2011 9:44 PM
    Moderator
  • what do you mean by missing forieghn keys... 

    There is no way SQL Server will establlish that realtionship for you.... You have to go through your DDL and the requirement manually....

     


    Nothing is Permanent... even Knowledge....
    My Blog
    Tuesday, August 23, 2011 9:45 PM
  • Hello, 

    I just checked your blog....

    It looks like you are checking with the column name which are ending with ID (correct me if I am wrong), but this may not be true always to find all the columns...

     


    Nothing is Permanent... even Knowledge....
    My Blog
    Tuesday, August 23, 2011 10:03 PM
  • Yes, that's just an assumption. BTW, it's blog by George Mastros, not mine.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Tuesday, August 23, 2011 10:07 PM
    Moderator
  • You can query the information schema tables.  There's a script that should be able to provide that information.  I use that in my code repository.  Let me know if you need a copy.

     


    Greg http://www.freewebstore.org/tsqlcoderepository Powerful tool for SQL Server development
    Wednesday, August 24, 2011 12:53 AM
  • Please provide that script.
    Wednesday, August 24, 2011 6:28 AM
  • Hi

    The story here is one of my client asked me to send a Schema of DB. I generated schema and sent to him.

    He raised a doubt that this DB contains lot of FK's but your script contians only few FK's.

    You better to write a DDL script to find missing FK's.

    I used below script to locate all foreign key in DB:

    SELECT * FROM sys.all_objects WHERE type =

    'F'

    SELECT

    * FROM sys.foreign_keys

    SELECT

    * FROM

    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

    is this scripts are sufficient to know all FK's in DB or anything i have to modify?

     

     

    Wednesday, August 24, 2011 6:33 AM
  • The following article deals with the same topic:

    http://www.sqlusa.com/bestpractices2005/primaryandforeignkeys/

     


    Kalman Toth, SQL Server & Business Intelligence Training; sqlusa.com
    Monday, August 29, 2011 5:15 AM
    Moderator