How do I combine these two SQL statements into one, in a manner compatible with both MSSQL 2000 and 2005?

Question

My end goal is to accomplish something like:

CREATE FOREIGN KEY IF NOT EXISTS FOREIGN KEY

Since that statement doesn't appear to exist, I'm attempting to sort of kludge it together.

I have a statement that will return the FK name if it exists:

SELECT f.name AS ForeignKey
FROM sys.foreign_keys AS f 
WHERE OBJECT_NAME(f.parent_object_id) = 'myTableName'

And I have a statement that adds the desired foreign key:

ALTER TABLE myTableName
WITH CHECK 
ADD CONSTRAINT [FK_myTableName_otherTable] 
FOREIGN KEY([columnName]) 
REFERENCES otherTable ([otherColumn])

I just can't, for the life of me, figure out how to mash them together into a single statement.

It's an absolute requirement that the solution work in both MS SQL 2000 as well as MS SQL 2005.

Answer 1

if not exists (SELECT f.name AS ForeignKey
FROM sys.foreign_keys AS f 
WHERE OBJECT_NAME(f.parent_object_id) = 'myTableName'
)
begin
    ALTER TABLE myTableName
    WITH CHECK 
    ADD CONSTRAINT [FK_myTableName_otherTable] 
    FOREIGN KEY([columnName]) 
    REFERENCES otherTable ([otherColumn])
end

Answer 2

if not exists (
    SELECT f.name AS ForeignKey
    FROM sys.foreign_keys AS f 
    WHERE OBJECT_NAME(f.parent_object_id) = 'myTableName') 
begin
    ALTER TABLE myTableName
    WITH CHECK 
    ADD CONSTRAINT [FK_myTableName_otherTable] 
    FOREIGN KEY([columnName]) 
    REFERENCES otherTable ([otherColumn])
end

Answer 3

sys.foreign_keys was introduced in SQL Server 2005. You'll have to use sysobjects to be compatible with both SQL Server 2000 & 2005.

Try this SQL:

IF NOT EXISTS (
    SELECT NULL FROM sysobjects 
    WHERE name = 'FK_myTableName_otherTable' 
    AND parent_obj = OBJECT_ID(N'myTableName'))
ALTER TABLE myTableName
WITH CHECK 
ADD CONSTRAINT [FK_myTableName_otherTable] 
FOREIGN KEY([columnName]) 
REFERENCES otherTable ([otherColumn])