INSERT statement conflicted with the FOREIGN KEY contraint

Violeta Popa

Hi :)

I have 2 tables

Client(client_id(pk), first_name, last_name, user_id(fk))
User(user_id(pk), password, client_id(fk)). (my database is more complex, this is just an exemple).

And I'm trying to insert a new record in my database, but i just keep getting an error from the foreign key, because of the one to one relationship. I've tried to use a default value for user_id in client, but the same error. Does anybody know how to solve this pb? please :(

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vishal Gilbile13y ago
Hello Friend,
Error you are getting because you are creating a primary key and foreign key relationship between both the table by assigning a primary key of table as foreign key in another and vice versa. From my point of view if you want to perform this kind of operation you should simply adopt normalization feature in your DB structure.

Error you are getting because if you look at your table structure one table accepts foreign key of another table and when you try to add a record in one table your corresponding entry of primary key needs to be there in the parent table. The better approach to solve this would be

Normalize your table structure like the below.

Client(client_id(pk), first_name, last_name)
User(user_id(pk), password)

NewTable(clientID(fk),userId(fk);

Hope that solves your problem.

With Regards,
Vishal Gilbile
0
Javeed M Shaikh13y ago
the only way you can do this is by removing the foreign key from the original table and create a third table with user_id and client_id or make the foreignkey in one of the table as nullable. in both cases you have to change your design.

to answer your question you can use the
SELECT SCOPE_IDENTITY() or @@IDENTITY

to get the last identity value in your scope and use that value to populate the child table.
0
Violeta Popa13y ago
Like i said this is just an exemple, i'm interested in how to add new record in one to one relationship. My database is created and is correct.
0
Javeed M Shaikh13y ago
how is the relation defined between these 2 tables:

I think it should be

Client
client_id (PK)
first_name
last_name

User
User_id (PK)
client_id (FK)
password

Insert in the Client table first, and use the client_id to populate the User table.
0