Does SQL manage foreign key constraints in terms of insertion?
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 "&lt;name of constraint&gt;" on table "&lt;your table&gt;".

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

SQL Server:

statement conflicted with the FOREIGN KEY constraint "&lt;name of contraint&gt;". Conflict occured in database &lt;your database&gt;

Postgres:

insert or update on table "&lt;your table&gt;" violates foreign key constraint
"&lt;constraint name&gt;"

Oracle:

ORA-02291: integrity constraint (&lt;schema.constraint&gt;) 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.