How do primary key , foreign key and unique constraints work? i mean in what sequence?

Like, when a child table has a FK, and a record is inserted into it , which doesn't exists in the parent table, then is this record first inserted into the child table & then the constraint checks in the Parent table if this record exists or not, and if it doesn't finds it then it rollbacks and removes the record from the Child table. is this the order of working?

or, does first SQL gets the record(on which the FK is made) from the insert query, & matches it with the parent table records, and ceases the insert when matching record is not found, while insertion itself and doesn't inserts the row in the child table?

Similarly, for the primary key, if a duplicate record is inserted in a table, then is it first inserted then checked or before insertion first it is matched with existing records, and if it is a duplicate one, then the query is ceased.

3 Answers
3

Logically speaking, all constraints are supposed to be checked simultaneously against the entire result of an UPDATE, INSERT or DELETE statement. The constraints are evaluated as if the modification to all rows had already happened and if any constraint would be violated then the modification is not permitted.

sir, so what do you mean, am not able to understand with reference to my post, i mean so what's the order of execution? is first the data inserted and then checked OR first checked and then inserted?
–
sqlchildMar 29 '11 at 5:00

if there's a previously existing NULL value, then the PK would stop inserting another NULL, thus working successfully, then why isn't a NULL value allowed?
–
sqlchildMar 28 '11 at 6:01

@sqlchild: Probably it's because of the fundamental idea that a key must be a value. (NULL in this context is considered to be a non-value.)
–
Andriy MMar 28 '11 at 8:30

2

Because a key is a set of attributes containing a set of unique values whereas nulls are not values. The practice of treating nulls as if they were equivalent values in uniqueness constraints is a peculiar feature of the SQL Server family of DBMSs. Other DBMSs don't do that. Nullable unique constraints are generally best avoided altogether - they don't really make much sense.
–
sqlvogelMar 28 '11 at 10:27

Consider the logical (conceptual) tables deleted and inserted that are accessible to a TRIGGER. Even these are only concepts. Who knows what's going on under the covers? ...well, someone is bound to know... but do you care what's going on under the covers? At the conceptual level, it either succeeds or fails or you can manipulate the outcome in a trigger. What more do you need to know? ;)

Actually there are some cases where things aren't so simple and it IS useful to understand what is going on under the covers. For example SQL Server will evaluate some constraints before others and only return one error rather than all the errors. Also CHECK constraints get evaluated on a row-by-row basis rather than set-based. That can cause some nasty problems if you have complex code that accesses multiple rows in a CHECK constraint.
–
sqlvogelMar 28 '11 at 11:20