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

Question

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?

Answer 1

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)
)

Answer 2

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.