Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

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?

share|improve this question
1  
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? – fdart17 Oct 17 '12 at 0:42
Typically any decent sql engine will prevent "orphan" records from being created by INSERTs and DELETEs. You will get an error if you attempt to insert a child record that doesn't have a parent. That said, you should try it to really get a feel for how it works in your environment. – Max Vernon Oct 17 '12 at 1:07
Catching that situation is the whole reason we have foreign key constraints. – a_horse_with_no_name Oct 17 '12 at 21:20

1 Answer

up vote 8 down vote accepted

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. Then when you see the error it makes it easy to identify the tables involved since almost all of them name the constraint.

share|improve this answer
I think you should add that MySQL has "engines". Some of them have FKs implemented (like InnoDB) while others do not (MyISAM, Memory) – ypercube Oct 17 '12 at 22:33
I've only ever worked with Inno - so that is something I was not aware of. Thanks for the info. A quick google pointed out Falcon does as well. Great input. – RThomas Oct 17 '12 at 22:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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