How to prevent inserting duplicate rows based on two foreign keys?

1.6k views Asked by At

If I am manually inserting hundreds of rows, how should I efficiently write the insert statements so that each insert will not run only if both foreign keys are already present together in a record in that database?

2

There are 2 answers

0
BrianAtkins On

Create a constraint on the columns, here is a great reference starting point.

Here is an example of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)
0
Ilan On

You can add a unique constraint as described by @BrianAtkins and you can also use a MERGE statement instead of INSERT in order to avoid breaking the entire insert on a unique constraint violation.