0

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'.

Embid123
  • 467
  • 5
  • 17
  • Sounds like you want an [`AFTER INSERT`](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-2017) trigger. – S3S Jan 21 '19 at 21:16
  • From the tables you posted having two tables here seems unnecessary. The second table is nothing more than a single nvarchar(50) in STAN. Why make it more complicated than it needs to be? Sure there are times where a separate table makes sense but this doesn't seem to be one of them. – Sean Lange Jan 21 '19 at 22:18

1 Answers1

6

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);
David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • 4
    And, as usual, the two inserts should be wrapped in a transaction if you want to ensure that either both succeed or neither succeeds. – HABO Jan 21 '19 at 22:16