Checking Foreign Keys in script

I have the following in an update script.
ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
(
[ProductCode]
) REFERENCES [dbo].[ProductLookup] (
[ProductCode]
)
GO
I would like to check if the FOREIGN KEY exists and delete or use an if
statement to prevent the following error messages
Server: Msg 2714, Level 16, State 4, Line 2
There is already an object named 'FK_SoftwareRentalMaster_ProductLookup' in
the database.
Server: Msg 1750, Level 16, State 1, Line 2
Could not create constraint. See previous errors.
How can i do this
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004Hi,
Use the below sample of script. Replace the table name, constraint name and
column names based on ur requirement.
if exists(select constraint_name from
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS where constraint_name ='fk_i')
Begin
select 'Foreign Key exists'
select 'dropping the Foreign Key constraint'
alter table x_2 drop constraint fk_i
end
select 'Creating the Foreign key constraint back to table'
Alter table x_2 add constraint fK_i foreign key (i) references x_1(i)
Thanks
Hari
MCDBA
"Jeff Williams" <jeff.williams@.hardsoft.com.au> wrote in message
news:uKPPPvFREHA.3728@.TK2MSFTNGP10.phx.gbl...
> I have the following in an update script.
> ALTER TABLE [dbo].[SoftwareRentalMaster] ADD
> CONSTRAINT [FK_SoftwareRentalMaster_ProductLookup] FOREIGN KEY
> (
> [ProductCode]
> ) REFERENCES [dbo].[ProductLookup] (
> [ProductCode]
> )
> GO
> I would like to check if the FOREIGN KEY exists and delete or use an if
> statement to prevent the following error messages
> Server: Msg 2714, Level 16, State 4, Line 2
> There is already an object named 'FK_SoftwareRentalMaster_ProductLookup'
in
> the database.
> Server: Msg 1750, Level 16, State 1, Line 2
> Could not create constraint. See previous errors.
> How can i do this
> Regards
> Jeff
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.691 / Virus Database: 452 - Release Date: 26/05/2004
>