Does SQL manage foreign key constraints in terms of insertion?

Question

Say I have a table tbl1 with data A1 and A2, where A1 is the primary key. Then I have another table tbl2 with data A3 and A1, both together being the primary key, referencing A1 as a foreign key from tbl1.

Am I able to insert a tuple into tbl2 that has an A1 that is not in tbl1? Does SQL manage this for us as an error? Or what happens in this situation?

Answer 1

As discussed in the comments violation of a foreign key constraint will produce an error on most legitimate server products. However, this is only when the foreign key has been defined physically.

A logical foreign key can exist without the enforcer that on most engines is called the constraint. I've seen a lot of schemas that show relations on paper but on close inspection revealed that logical relationship wasn't actually being enforced by a foreign key constraint. This is important to check for data integrity purposes as well as query performance when relationships are properly indexed as well.

When the constraint has been defined then each of the following server products will generate an error if you violate that constraint.

MySQL:

violation of FOREIGN KEY constraint "<name of constraint>" on table "<your table>".

note MySQL currently only supports Foreign Key constraints natively with InnoDB & Falcon engines

SQL Server:

statement conflicted with the FOREIGN KEY constraint "<name of contraint>". Conflict occured in database <your database>

Postgres:

insert or update on table "<your table>" violates foreign key constraint "<constraint name>"

Oracle:

ORA-02291: integrity constraint (<schema.constraint>) violated - parent key not found

Sybase's message is probably identical to SQL Server, I know DB2 enforces it. SQLite does as well though the error message doesn't identify the constraint.

Note these engines will also raise an error if you try to remove the parent once children relying on the key exist.

Another tip:

When you look at these errors you also pick up on a good development strategy. Name your constraints in a way that makes it easy to track back to the tables involved since almost all of them identify the constraint name in the error.

• user1822: Another thing worth mentioning is that even InnoDB ignores some foreign key constraints without telling you: sqlfiddle.com/#!2/73417/1