[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Sql 2005 database has a foriegn key constraint error

Posted on 2008-11-19
Medium Priority
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

brad2575 earned 750 total points
ID: 22996810
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 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 750 total points
ID: 23783605
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

873 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