INSERT statement conflicted with the FOREIGN KEY contraint

Question

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 :(

Vishal Gilbile · Answer

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

Javeed M Shaikh · Answer

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.

Violeta Popa · Answer

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.

Javeed M Shaikh · Answer

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.