I would like to connect 3 tables. I can bring the first two tables in relationship, but the 3. table has already data, that is not identical with the contents from the other table. Because of that I couldn`t create a relationship with primary and foreign key.

Is there any way to solve this problem?

I am working on a project, I transfer data with API in our intern database , that is the reason, that one of the 3 tables is not empty.The transferred data must be saved in this partially filled table.

I transfer the data between the tables using cursor.

CodePudding user response：

It is possible to create a foreign key that will not be enforced against existing data in the table, but will be enforced for any subsequent inserts.

Here is an example:

create table T(i int primary key)

insert T (i) values (1);

create table U(j int);

insert U (j) values (2);

alter table U
with nocheck -- this is the part that ignores existing data
add constraint MyForeignKeyConstraintName 
foreign key (j) references T(i);

However, as Larnu implies with their comment, you should be very careful when deciding whether this is something you actually want to do. Do you really want to have data in the table that is in violation of the foreign key?

If the answer is "yes, but we will fix the data later and then enforce the foreign key for all rows", then that is also supported. Once you have fixed the data, you would run a statement like the following:

alter table U with check check constraint MyForeignKeyConstraintName;

Yes, the double "checK" is correct. The "with check" means "this key will be enforced against existing data", and the subsequent "check" means "enable this enforcement".