add constraint with a new name everytime

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

add constraint with a new name everytime

Author

Topic

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-06-06 : 13:55:21

i have the following step in a stored procedure

Alter table ABC
Add constraint Primary_KEY_ABC
PRIMARY KEY (DATE, Amount)

but i get the error primary key already exists.
So is there any way I can generate a unique name everytime I run the
proc, its runs only once a day so can i use getdate() in the name somehow. is it possible.

Ashley Rhodes

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-06 : 14:00:14

Its better to check if the constraint exists, then drop it and recreate it. just changing the name will not suffice.
Query sysconstraints, IF EXISTS do an ALTER TABLE DROP CONSTRAINT, then do your ADD CONSTRAINT.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-06-06 : 14:13:06

for some reason it says constraint does not exists.

but i can see the constraint and the index with the name when i do sp_help table

Ashley Rhodes

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-06 : 14:18:05

so how are you checking for the constraint? Post your script..

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-09 : 06:57:45

quote:
Originally posted by ashley.sql

i have the following step in a stored procedure

Alter table ABC
Add constraint Primary_KEY_ABC
PRIMARY KEY (DATE, Amount)

but i get the error primary key already exists.
So is there any way I can generate a unique name everytime I run the
proc, its runs only once a day so can i use getdate() in the name somehow. is it possible.

Ashley Rhodes

Why do you want to add constraint everytime you run the procedure?
Alter it when needed. Dont put it in SP

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources