June 9, 2010 at 12:02 pm
I am trying to run the following code:
ALTER TABLE [dbo].[Base_table] ADD CONSTRAINT [FK_Base_tableHP] FOREIGN KEY ([oid]) REFERENCES [dbo].[Base_table1] ([oid])
GO
ALTER TABLE [dbo].[Base_table] ADD CONSTRAINT [FK_Base_tableHP1] FOREIGN KEY ([child_oid]) REFERENCES [dbo].[Base_table1] ([id])
I get this error message:
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_Base_tableHP1". The conflict occurred in database "qqqqq", table "dbo.Base_table1", column 'id'.
How can I fix it? Thank you
June 9, 2010 at 12:41 pm
based on the error it sounds like the foreign key constraint already exists. I would look in management studion and verify what leys and constriants already exist.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 9, 2010 at 12:43 pm
As a guess, there's data existing in the tables that violates the constraint you're trying to add.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2010 at 12:50 pm
I just re created table and I am ok,bit can't add data, what I need to do in order to insert data,should I disable a key and then re create it?
June 9, 2010 at 12:53 pm
I apologize for my silly post before. I looked and I was thinking there was a more specific error when there was a violation. once again the filling drawer in my head is broke. At any rate here is another take.
This error can occur when creating a FOREIGN KEY constraint on a table and the values from the columns specified in the FOREIGN KEY constraint does not exist in the values of the columns designated as the PRIMARY KEY on the other table.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 9, 2010 at 12:55 pm
Krasavita (6/9/2010)
what I need to do in order to insert data,should I disable a key and then re create it?
No. Why would you want to do that?
What error are you getting that you can't insert data? Fix whatever the error's complaining about.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2010 at 12:55 pm
Krasavita (6/9/2010)
I just re created table and I am ok,bit can't add data, what I need to do in order to insert data,should I disable a key and then re create it?
it's an either or thing; you can either add the data that complies with the constraint, or insert the data without the constraint;
did you do this select and find out what data does nto comply with the constraint yet?
SELECT child_oid,* FROM [Base_table] WHERE child_oid NOT IN(SELECT id FROM Base_table1 WHERE id is not null)
Lowell
June 9, 2010 at 1:20 pm
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Base_CatalogHP". The conflict occurred in database "database1", table "dbo.Base_table1", column 'id'.".
I get this error. I am trying to copy table from one server to another with the data. What is the best way to do it?
June 9, 2010 at 1:43 pm
Populate the parent table first?
I assume, by putting a foreign key in place, you don't want any values in the one table that aren't in the other. Therefore, you need to make sure that all the necessary values are in the parent table before you populate the child table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2010 at 1:52 pm
Krasavita (6/9/2010)
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Base_CatalogHP". The conflict occurred in database "database1", table "dbo.Base_table1", column 'id'.".I get this error. I am trying to copy table from one server to another with the data. What is the best way to do it?
To troubleshoot this problem a little further.
If you are populating the parent table first and then the table where you're getting the error, run a query to see if you have any records in the table with the foreign key without a match in the parent table
Example:
SELECT P.MainKeyID
FROM ParentTable P
LEFT JOIN ReferringTable R
ON P.MainKeyID = R.MainKeyID
WHERE R.MainKeyID IS NULL
[/Code]
If you get any records back it means you're missing data in the table you're trying to reference.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
June 9, 2010 at 1:56 pm
Krasavita (6/9/2010)
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Base_CatalogHP". The conflict occurred in database "database1", table "dbo.Base_table1", column 'id'.".I get this error. I am trying to copy table from one server to another with the data. What is the best way to do it?
As Gail said here, you need the parent data first.
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
June 10, 2010 at 1:14 pm
How about providing the DDL (CREATE TABLE statements) for the table(s) involved (including the constraints applied), sample data you are trying to insert (series of INSERT INTO statements) for the table(s) involved, and the expected results when the inserts are done.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply