menu
  • Register

The alter table statement conflicted with the foreign key constraint.

1 vote
36 views

Problem :

I am very new to the Sql Server. Currently I want to add the new foreign key to the already existing table in this table there is existing data in a column where I want to make the change.

I tried this approach on a table for a column where data is present and also for a column data does not exist. I have noticed where there is no data it works fine. Please find below the error message.

“The alter table statement conflicted with the foreign key constraint”.

Can someone guide me in fixing this foreign key error?

●8 ●5 ●2
3,230 points

Please log in or register to answer this question.

1 Answer

1 vote

Solution :

I think you are facing above error because you may have the orphaned records in your table. The orphaned record is the record which is present in the child table without having the corresponding parent record in its parent table.

To fix the problem you need to cleanup your data.To cleanup your data you must first find the records in your child table which do not have the corresponding parent record in parent table. After that you need to either delete or update the required records in your child table or you can add your missing parent records in to your required parent table. Afterwar doing this exercise you should be able to create your foreign key constraint.

●8 ●4 ●1
5,680 points

Related questions

0 votes
2 answers 49 views
49 views
insert statement conflicted with the foreign key constraint
asked Dec 30, 2020 miki 1.9k points
0 votes
1 answer 3 views
3 views
SQL DROP TABLE foreign key constraint
Problem: If I want to delete all the tables in my database like this, will it take care of the foreign key constraint? If not, how do I take care of that first? GO IF OBJECT_ID('dbo.[Course]','U') IS NOT NULL DROP TABLE dbo.[Course] GO IF OBJECT_ID('dbo.[Student]','U') IS NOT NULL DROP TABLE dbo.[Student]
asked Apr 10 Atik03 26.5k points
0 votes
1 answer 24 views
24 views
the delete statement conflicted with the reference constraint
the delete statement conflicted with the reference constraint
asked Feb 16 ArifulIslam 7.5k points
0 votes
1 answer 3 views
3 views
Alter table add foreign key sql server
Problem: I get this error message: Error Code: 1005. Can't create table 'mytable.#sql-7fb1_7d3a' (errno: 150)
asked Apr 19 ummesalma 29.2k points
0 votes
1 answer 58 views
58 views
Cannot define primary key constraint on nullable column in table.
Problem: I am able to create the table named employee which contains the column empid which is a computed column and empid column is the primary key also. It is the self referencing table and the column managerid references back to itself. But now if I try to save ... error my table is still working perfectly. I am trying to work in a table graphically not coding every time I make the change.
asked Jul 4, 2020 Raphael Pacheco 4.9k points
0 votes
1 answer 5 views
5 views
Cannot truncate table because it is being referenced by a foreign key constraint.
Problem: Why doesn't a TRUNCATE on mygroup work? Even though I have ON DELETE CASCADE SET I get: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) REFERENCES ... NULL, DateTime DATETIME DEFAULT NULL, FOREIGN KEY (GroupID) REFERENCES mygroup(ID) ON DELETE CASCADE, UNIQUE(GroupID) ) ENGINE=InnoDB;
asked Apr 26 sumaiya simi 43.9k points
0 votes
1 answer 40 views
40 views
Cannot truncate a table referenced in a foreign key constraint.
Problem: I am unable to understand why a TRUNCATE on mygroup is not working? Even though I have it ON DELETE CASCADE SET I encounter below strange error: ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, ...    DateTime     DATETIME DEFAULT NULL, FOREIGN KEY  (GroupID) REFERENCES mygroup(ID) ON DELETE CASCADE,    UNIQUE(GroupID) ) ENGINE=InnoDB;
asked Jul 23, 2020 Raphael Pacheco 4.9k points
0 votes
1 answer 6 views
6 views
Alter table student add check(s_id 0)
Problem: Unable to create the tables due to: Alter table student add check(s_id 0). So, what's the problem in here?
asked Apr 22 anika11 32k points
0 votes
1 answer 7 views
7 views
Alter table modify column sql server 2008
Problem: How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5)
asked Apr 19 ummesalma 29.2k points
0 votes
1 answer 1 view
1 view
Which of the following statements about the foreign key constraint is incorrect?
Problem: I know this question has been asked many times, but none of the answers have solved my issue. I am creating a database for a uni assignment, using PostgreSQL through pgadmin 4, and I have a table named "staff" populated with staff members with a primary key of " ... Dogs', 'Border Patrol', 'Carol Moore', '07911 653453', '*Emails are not allowed*', 'false', '500000001', '100000024');
asked Apr 26 anika11 32k points
Dark theme