sql-servert-sqlsql-insertshared-primary-key

Insert rows to 1:1 related tables

I want to put new rows into tables related 1:1 in sql-server. diagram of those tables

As you see, tables reference each other with the same primary key. And here is the problem- I know that putting data with two INSERT calls will throw me an error saying I violate PK constraint. Is there a way of doing this in-code? I want to create a stored procedure used for adding "primary products" (SUROWCE) and corresponding to it record in "stock" (STAN).

My code attempt: INSERT INTO STAN VALUES(25, 5, 1000); INSERT INTO SUROWCE VALUES(25, 'wood');

Msg 547, Level 16, State 0, Line 4 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_STAN_SUROWCE". The conflict occurred in database "TestBazyDanych", table "dbo.SUROWCE", column 'ID_SUROWCA'.

Solution

  • In a 1:1 relationship, only one table has a Foreign Key referencing the other. So you insert into the table without the foreign key first. Try simply reversing the order of the inserts:

    INSERT INTO SUROWCE VALUES(25, 'wood');
INSERT INTO STAN VALUES(25, 5, 1000);