Sql 2005 database has a foriegn key constraint error

Posted on 2008-11-19
Last Modified: 2012-05-05
I have a database that keeps logging errors as a foriegn key constraint error. I'm an infastructure guy so sql is pretty much a mystery to me. i've attached the error, the customer says that the database is working but have concerns about the error. Can I get some help from you SQL guru's out there please?
Question by:jasin00
    LVL 16

    Accepted Solution

    There is an error in the database "SMS_AR_Report"

    stating that you are trying to insert a value into the table "dbo.Topics" in the column 'topic_ID'.

    This topic_ID is the foreign key (meaning that this ID has another table that it looks at to see if the number being put here exists in that other table, and if not cause this error).  So the insert statement it is inserting an ID in this field that does NOT exist in the other table.  

    Unless I missed it, it does not tell me what table has the foreign key in it that needs to have the topic_id inserted into before the error will not work.

    You need to look at this Foreign key in "Topics"  "FK_HST_SuspectData_Topics".   and this should give you the details you need.
    LVL 24

    Assisted Solution

    by:DBAduck - Ben Miller
    The concept is this:

    You declare a Primary Key in the Primary table that is the parent.  Then in the child table that will have Foreign keys (related records to the Parent) you declare a Foreign Key constraint, that basically says, for every key in this child table (in your case Topic_ID) there must exist a Parent record with the same Topic_ID in the Parent Table (Primary table).

    So I have this:
    ParentTable (Topic_ID) declared as Primary Key
    I insert Topic_ID 1000 into that table.

    ChildTable (Topic_ID) declared as Foreign Key pointing to the Primary Key (PrimaryTable.Topic_ID)
    I insert Topic_ID 1000 into that table, everything is OK.
    I insert Topic_ID 1001 into that table, ERROR, there is no 1001 in Primary Key table, only 1000.

    What this does is keep there from being Orphans in the Foreign Key table (Children without Parents) so that your data has integrity referentially.  This way I will never have to worry about the child table having rows that do not relate to the parent table.

    Hope this makes sense.  So the long and the short of it is, the inserts that are happening on the Foreign Key table and throwing errors are because the Parent table does not contain that Topic_ID, therefore it is a violation.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
    In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now