How to add foreign keys with IF EXISTS/IF NOT EXISTS statement?

Question

Im trying to insert a foreign key into multiple tables by checking if the key exists already, if not it should insert it. But when I try to run my script it just skips the whole insert part, even when all the individual commands work properly. (i use MS SQL Server 2008)

begin transaction;

declare @isid as nvarchar(50);
declare @fk as nvarchar(50);
declare @statement as VARCHAR(2000);

declare @db_cursor CURSOR;
set @db_cursor = cursor FOR SELECT Name FROM [sys.Component] order by Name;

open @db_cursor;
fetch next from @db_cursor into @isid;

while @@FETCH_STATUS = 0
begin
    SELECT @fk = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = 'FK_' + @isid + '_Firma';
    if not exists (select @fk) 
        begin
        print 'NULL: ' + @fk;
        select @statement = 'ALTER TABLE ' + @isid + ' add constraint FK_' + 
           @isid + '_Firma FOREIGN KEY(FirmaCode) REFERENCES Firma(FirmaCode)';
        exec(@statement);
    end
    print 'NOT NULL: ' + @fk;
    fetch next from @db_cursor into @isid;
end
close @db_cursor;

commit transaction;

basic output i get is 'NOT NULL: FK_<TableName>_Firma'

Thank you for any help you can provide with my problem.

Answer 1

Try

begin transaction;

declare @isid as nvarchar(500);
declare @fk as nvarchar(500);
declare @statement as VARCHAR(2000);

declare @db_cursor CURSOR;
set @db_cursor = cursor FOR SELECT Name FROM [sys.Component] order by Name;

open @db_cursor;
fetch next from @db_cursor into @isid;

while @@FETCH_STATUS = 0
begin
select @fk = null
    SELECT @fk = CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS 
    WHERE CONSTRAINT_NAME = 'FK_' + @isid + '_Firma';
    if (@fk is null) 
        begin
        print 'NULL: ' + @fk;
        select @statement = 'ALTER TABLE ' + @isid + ' add constraint FK_' + 
           @isid + '_Firma FOREIGN KEY(FirmaCode) REFERENCES Firma(FirmaCode)';
        exec(@statement);
    end
    print 'NOT NULL: ' + @fk;
    fetch next from @db_cursor into @isid;
end
close @db_cursor;

commit transaction;