Posted by Erland Sommarskog on 10/01/00 11:41

(heyvinay@gmail.com) writes:
> We can manage all this from code directly to ensure all is in sync, but
> I prefer to apply referential integrity managed by the DBMS.

Referential integrity is about the integrity of - references. That is,
if the Orders table has a FK constraint to table Customers, you cannot
add an Order for a non-existing customer, and you cannot delete a customer
that has an order.

As I understood it, you want one data in one table be the result of data
in other tables. This cannot be achieved with referential integrity. You
can use views, and under some circumstances you can materialise a view.

> You wrote: "For that to work, the Transaction table need to have one FK
> column per referencing table. " - does MS SQL allow null values for
> foreign key constraints?

Yes. For instance, in a Customers table, you may have Citizen column
table that has a foreign-key constraint to the Countries table. But
this column must be nullable, since some people stateless. Not talking
of juridical persons, who normally are not citizens.

--
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]