Alter table query

[Bastien]

I have a database that was converted from mysql to mssql and I need to put all the primary keys back. I have codged togther this sample, but having some issues with the query.

Any clues as to where I messed up?

IF EXISTS (SELECT * 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' 
AND TABLE_NAME = 'access_group' 
BEGIN
ALTER TABLE access_group ADD PRIMARY KEY (access_group_id, access_logon_id, access_start_date);
END

IF EXISTS (SELECT * 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' 
AND TABLE_NAME = 'access_guest' 
BEGIN
ALTER TABLE access_guest ADD PRIMARY KEY (parent_number, guest_logon_id, access_start_date);
END

...

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[bborissov]

You missed the closing brackets in the IF statements:

CODE
IF EXISTS (SELECT *
                  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
           WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
           AND TABLE_NAME = 'access_group'[b])[/b]
BEGIN
ALTER TABLE access_group ADD PRIMARY KEY (access_group_id, access_logon_id, access_start_date);
END

IF EXISTS (SELECT *
                  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
           WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'
           AND TABLE_NAME = 'access_guest'[b])[/b]
BEGIN
ALTER TABLE access_guest ADD PRIMARY KEY (parent_number, guest_logon_id, access_start_date);
END

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Bastien]

Thanks, that was it

Bastien

I wish my computer would do what I want it to do,
instead of what I tell it to do...

 

[bborissov]

That is actually refers to your signature

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.