Posted by Hugo Kornelis on 06/26/07 20:14

On Mon, 25 Jun 2007 21:07:00 -0700, nyathancha@hotmail.com wrote:

>Actually, another best practices question now that I am here. Does it
>make sense for a table to have two (or more different foreign keys)
>both (or all) of which can be nullable and then tie them to different
>tables for different records?

Hi nyathancha,

This pattern isn't uncommon:

CREATE TABLE SomeTable
(SomePrimKey int NOT NULL,
FirstForeignKey int NULL,
SecondForeignKey int NULL,
-- Other columns,
PRIMARY KEY (SomePrimKey),
FOREIGN KEY (FirstForeignKey) REFERENCES SomeTable,
FOREIGN KEY (SecondForeignKey) REFERENCES OtherTable,
CHECK ((FirstForeignKey IS NULL AND SecondForeignKey IS NOT NULL)
OR (FirstForeignKey IS NOT NULL AND SecondForeignKey IS NULL))
);

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]