how to check and alter constraints

Question

hi can anyone tel me how to check whether a primary key exists or not in a table and add a primary key if not exixts in sql server compact(.sdf)..

i'm using this,

  IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY') 
   BEGIN 
       alter table [tablename] add constraint [name] PRIMARY KEY (columnname)
   END

when i execute this in sql server compact i get this error..

Major Error 0x80040E14, Minor Error 25501

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY') BEGIN alter table [tablename] add constraint [name] PRIMARY KEY (columnname) END There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]

thank you..

It would appear that SSCE doesn't support IF. – Will A Aug 28 '10 at 09:28 — Will A, Aug 28 '10 at 09:28
ok then how do i check any help?? – Leema Aug 28 '10 at 10:21 — Leema, Aug 28 '10 at 10:21

score 0 · Answer 1 · edited Jun 04 '15 at 13:23

0

 IF NOT EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'PRIMARY KEY') AND type in (N'U') )
   BEGIN 
       alter table [tablename] add constraint [PRIMARY KEY] PRIMARY KEY CLUSTERED (columnname)
   END

Please use the above query and let me know if it works. thanks.

edited Jun 04 '15 at 13:23

Jeen Broekstra

21,642
4
51
73

answered Jun 04 '15 at 10:34

Mahesh Kumar

9
2

how to check and alter constraints

1 Answers1