Posted by Erland Sommarskog on 02/23/07 23:06

Emin (emin.shopper@gmail.com) writes:
> When I use a single table I can easily use constraints to enforce my
> business logic, but what do I do when I normalize a single table into
> multiple tables.
>
> For example, imagine that my initial table has the columns ID, Name,
> Salary with the constraint that Salary is not NULL. Now imagine that
> I break this into two tables, one with ID and Name and another with ID
> and Salary. I would like to have a constraint that prevents the
> creation of a row with (ID,Name) in the first table unless a
> corresponding row in the second table is also created.
>
> I can enforce this logic with triggers, but it looks ugly and is
> fairly brittle. Is there a better way or is this the dark side of
> normalization?

I wouldn't call that particular example normalistion, but rather
vertical partitioning. :-)

If you have

CREATE TABLE leftside (id int NOT NULL PRIMARY KEY,
name varchar(34) NOT NULL)
CREATE TABLE rightside (id int NOT NULL PRIMARY KEY,
salary int NOT NULL)

You can use a foreign-key constraint to ensure that an id is not
inserted into leftside, if it's not also in rightside.

However, there is no way that you can ensure that there are rows in
both table, at least not with useful data. Not with triggers, not
with constraints, since there are not any commit-time versions of
the same.

The best you can do is to have a trigger to cascade inserts with dummy
values into the other table.

More generally, cross-table checks you have to do with triggers,
with the exception of foriegn keys. (OK, you can use user-defined
functions that you call from you CHECK constraints, but that can
be very costly performancewise.)



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]