5

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?

  • 2
    What kind of SQL? SQL Server, MySQL, etc? Also, this has the smell of homework; if it is please add that to your post. – Max Vernon Oct 17 '12 at 0:26
  • I took a DB course and I'm teaching my coworker the basics, but I can't remember how this case is handled. In general, how is this handled? If it varies, how about MySQL? – Justin Oct 17 '12 at 0:42
11

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.