foreign key constraint issues

  • Hi, I have a table with a column ItemCode, what I want is to keep only those ItemCode which have been defined in the documents where the rest should be removed.

    My concern is I not sure whether other tables are referring to the rest of the ItemCode. Is it a way to find out this 'foreign key constraint' ? Thanks

  • Do you have table definitions, to include FK Constraints & sample data?

    Could you restate the question for it could easily be misinterpreted?

    For better, quicker answers on T-SQL questions, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • If you are trying to find which Foreign Keys reference a particular column, this link might get you going:

    http://blog.sqlauthority.com/2006/11/01/sql-server-query-to-display-foreign-key-relationships-and-name-of-the-constraint-for-each-table-in-database/

  • There are a number of ways that you can approach this. I tend to use the system views, because they have some of the necessary joins already set up and the information is more user friendly. Here is the a query that list child table, constraint, and parent table using the system views "Information_Schema.Table_Constraints" and "Information_Schema.Referential_Constraints".

    SELECT ct.Table_Name AS Child_Table, rc.Constraint_Name AS FK_Constraint_Name, pt.Table_Name AS Parent_Table

    FROM Information_Schema.Referential_Constraints AS rc

    INNER JOIN Information_Schema.Table_Constraints AS pt

    ON rc.Unique_Constraint_Name = pt.Constraint_Name

    INNER JOIN Information_Schema.Table_Constraints AS ct

    ON rc.Constraint_Name = ct.Constraint_Name

    It would be easy to add a where clause to this to filter for a particular parent or child table. If you need to know the specific columns, there is also a system view called "Information_Schema.Constraint_Column_Usage".

    Since the Information_Schema views are relatively new, many people who have worked with SQL Server for awhile tend to use the sys.objects view.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • It's not clear if you are looking for information on the constaint, or trying to find which data is linked to, or not linked to, FK values or PK values.

    If you can show in an example what you are trying to do, we can help. As it is now, it is not clear what your question is.

  • Are you trying to find which data exists in one table but not another?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

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

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