[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Checking if Table column constraint exists

steve

3/20/2007 6:31:00 AM

Hi All

I need to find out if a column constraint exists (i.e default value in a SQl
Server 2005 Table column)

The following code does not detect a constraint that exists and hence I get
an error that the constraint already exists

if not EXISTS (select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where
constraint_catalog = 'GeePos' and table_name = 'timekeepingdetails' and
Constraint_Name = 'DF_timekeepingdetails_hours')

begin

ALTER TABLE dbo.timekeepingdetails ADD CONSTRAINT
[DF_timekeepingdetails_hours] DEFAULT 0 FOR [hours]

end

Any ideas

Regards

Steve
3 Answers

Uri Dimant

3/20/2007 6:49:00 AM

0

Steve
See if this helps you

create table test1 (c int)
go
alter table test1 add constraint df_const default 0 for c
go

if not exists (select * from sysconstraints where constid=
object_id('df_const'))
print 'no'
else
print 'yes'



"steve" <ga630sf@newsgroups.nospam> wrote in message
news:uUq9WlraHHA.4552@TK2MSFTNGP05.phx.gbl...
> Hi All
>
> I need to find out if a column constraint exists (i.e default value in a
> SQl Server 2005 Table column)
>
> The following code does not detect a constraint that exists and hence I
> get an error that the constraint already exists
>
> if not EXISTS (select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where
> constraint_catalog = 'GeePos' and table_name = 'timekeepingdetails' and
> Constraint_Name = 'DF_timekeepingdetails_hours')
>
> begin
>
> ALTER TABLE dbo.timekeepingdetails ADD CONSTRAINT
> [DF_timekeepingdetails_hours] DEFAULT 0 FOR [hours]
>
> end
>
> Any ideas
>
> Regards
>
> Steve
>
>
>
>


Razvan Socol

3/20/2007 7:08:00 AM

0

If you don't know the name of the default constraint (you know only
the column name), you can use something like this:

IF NOT EXISTS (
SELECT * FROM sysconstraints
WHERE id=OBJECT_ID('TableName')
AND COL_NAME(id,colid)='ColumnName'
AND OBJECTPROPERTY(constid, 'IsDefaultCnst')=1
) ...

Razvan

steve

3/20/2007 9:15:00 AM

0

Hi Uri

Just what I wanted. Worked a treat

Great reply

many thanks
Steve


"Uri Dimant" <urid@iscar.co.il> wrote in message
news:Okb6%23vraHHA.5080@TK2MSFTNGP02.phx.gbl...
> Steve
> See if this helps you
>
> create table test1 (c int)
> go
> alter table test1 add constraint df_const default 0 for c
> go
>
> if not exists (select * from sysconstraints where constid=
> object_id('df_const'))
> print 'no'
> else
> print 'yes'
>
>
>
> "steve" <ga630sf@newsgroups.nospam> wrote in message
> news:uUq9WlraHHA.4552@TK2MSFTNGP05.phx.gbl...
>> Hi All
>>
>> I need to find out if a column constraint exists (i.e default value in a
>> SQl Server 2005 Table column)
>>
>> The following code does not detect a constraint that exists and hence I
>> get an error that the constraint already exists
>>
>> if not EXISTS (select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where
>> constraint_catalog = 'GeePos' and table_name = 'timekeepingdetails' and
>> Constraint_Name = 'DF_timekeepingdetails_hours')
>>
>> begin
>>
>> ALTER TABLE dbo.timekeepingdetails ADD CONSTRAINT
>> [DF_timekeepingdetails_hours] DEFAULT 0 FOR [hours]
>>
>> end
>>
>> Any ideas
>>
>> Regards
>>
>> Steve
>>
>>
>>
>>
>
>