Msg 547, Level 16, State 0, Line 2 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

Question

Post reply

Msg 547, Level 16, State 0, Line 2 The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

AK1516

SSCrazy

Points: 2705
More actions
October 15, 2010 at 12:22 pm

#229771

Msg 547, Level 16, State 0, Line 2
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
I have alredy created a new table but when i try to add the constraints gives the error above.Can any one help me with this.....?

Viewing 4 posts - 1 through 3 (of 3 total)

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

Johan Bijnens SSC Guru Points: 135268 More actions · Answer 1

Kind of like "I heard a brake noise ... now my dog is limping ... Why?"

Please post the ddl of your table and all of the current constraints (including FKs).

Also post the actual "alter table " statement you wanted to execute.

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

rellirel82 SSC Rookie Points: 39 More actions · Answer 2

I can't give you an answer to your question but I also has something similar to you. It errors out at my Insert Into statement. I believe it has something to do with the 'countries' table and the primary key that's inside which is 'country_code'.

This is the code that I put in:

set identity_insert customers on

select * from customers

insert into customers (custid, lname, fname, address, city, state, postal, country, mugshot, balance, monthly_fee)

values (101,'Johnson','Mike','101 Main st','Denver','CO','80231',0,null, 168.55, 25.00)

This is the error that I get:

(0 row(s) affected)

Msg 547, Level 16, State 0, Line 3

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_customers_Countries". The conflict occurred in database "deptstore", table "dbo.Countries", column 'country_code'.

The statement has been terminated.

Can someone explain what this means. I'm sure if this is answered it will answer both of our questions, thanks.

Johan Bijnens SSC Guru Points: 135268 More actions · Answer 3

rellirel82 (3/15/2011)
I can't give you an answer to your question but I also has something similar to you. It errors out at my Insert Into statement. I believe it has something to do with the 'countries' table and the primary key that's inside which is 'country_code'.
This is the code that I put in:
set identity_insert customers on
select * from customers
insert into customers (custid, lname, fname, address, city, state, postal, country, mugshot, balance, monthly_fee)
values (101,'Johnson','Mike','101 Main st','Denver','CO','80231',0,null, 168.55, 25.00)
This is the error that I get:
(0 row(s) affected)
Msg 547, Level 16, State 0, Line 3
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_customers_Countries". The conflict occurred in database "deptstore", table "dbo.Countries", column 'country_code'.
The statement has been terminated.
Can someone explain what this means. I'm sure if this is answered it will answer both of our questions, thanks.

Double check the country code you are inserting in your column 'country_code'.

It does not exist in your table Countries !!

In other words there is no pk in table Countries having a value of 0 (see your insert statement).