CODEWITHSUNDEEP
sqlsql-server
Ali Kherad
Ali Kherad

Reputation: 965

How can I insert into a table that is connected to another table with a 1-1 relationship


I dont know much about SQL, and I've got a problem using it.
I have two tables that connected to each other 1-1

Tbl1 (int_id1, str_desc1,....) and
Tbl2 (int_id2, str_desc2,....)

And these two are connected to each other int_id1 ---- int_id2

First I want to know that is my design true?
And how can I insert into one of these of two together.
Cause I've got problem when I try to insert into one

here's the error description:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Tbl2_Tbl1". The conflict occurred in database "project", table "dbo.Tbl1", column 'int_id1'.

Tnx...

Upvotes: 1

Views: 213

Answers (3)

World Python Developer
World Python Developer

Reputation: 61

Assuming you have 2 tables that have a 1to1 relationship. You can use a transaction to add rows to each. Lets say you have a Classes and Professors table, with a 1to1 relationship. Then you would do something like this:

begin tran;

insert into Classes (title) values ('Math 101');
insert into Professors(name) values ('Tim Rogers');

commit tran;

begin tran;

insert into Classes (title) values ('History 101');
insert into Professors(name) values ('Suzanne Bethany');

insert into Classes (title) values ('PE 101');
insert into Professors(name) values ('Emily Williams');

commit tran;


select * from Classes;
select * from Professors;

Upvotes: 0

M.Ali
M.Ali

Reputation: 69524

It means you are trying to insert a value in a Foreign Key Column which does not exist in the Primary Key Column which it referencing to.

Any value you add in a Foreign Key Column, It must Exist in the Primary Key Column to which it referencing to, after all that is the whole Idea of adding Foreign Key Constraints. so you will not end up having orphan records in a table and also it reduces data redundancy.

Read Here for more information about Foreign Key Constraints.

Upvotes: 1

jazza1000
jazza1000

Reputation: 4247

First I want to know that is my design true?

When you have tables in a one to one relationship, the first question should be: why not just use one table? There may not be a need to separate the data.

Upvotes: 0

Related Questions