Re: TSQL - Alter table to add a clustered prime key index

From: Wayne Snyder (wayne.nospam.snyder_at_mariner-usa.com)
Date: 09/10/04 

Date: Fri, 10 Sep 2004 06:28:36 -0400

to further explain your problems,
You can not have two constraints in a single definition ( Check and Primary
Key)
with Nocheck is not allowed with PK and unique constraints

Good luck 

-- 
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Rob" <Rob@discussions.microsoft.com> wrote in message
news:8C0DF46F-DDDA-4681-A065-1B6B6D32B6A2@microsoft.com...
> Hello All,
>
> I'm struggling to work out how to create a clustered prime key constraint
on
> an exisitent column in an existing SQL Server 2000 table.
> I need to do this in TSQL otherwise I would change the table design in
> Enterprise Manager.
> I've been playing with the syntax, but I just can't seem to nail it.
>
> My current TSQL is :
>
> ALTER TABLE USERS WITH NOCHECK ADD CONSTRAINT PK_USERS CHECK USERCODE
> PRIMARY KEY CLUSTERED
>
> TableName = USERS
> PrimeKeyColumnName = USERCODE
> DesiredPrimeKeyName = PK_USERS
>
> Any help much appreciated.
>
> Best Regards,
>
> Rob

Relevant Pages

  • Re: Unique index not recognizing null
    ... if they're not subject to the constraints of the Index?" ... consistent with other products - including SQL Server, Oracle, and how MS ... value in one or more of the key fields will not be included in the ... Nulls setting made no difference. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Use DEFAULT CONSTRAINTs or BOUND DEFAULTs?
    ... > I read SQL Server MVP Louis Davidson's post saying: ... > Constraints are the standard way to do this, and really should be the way ... > Even I read in the Microsoft SQL Server Introduction (SQL 7 book ... > DefaultZero ...
    (comp.databases.ms-sqlserver)
  • Re: Disabling Constraint for Bulk Loading
    ... I am working on ASE 12.5 ... Are you using Sybase SQL Server or Microsoft ... to disable constraints the command is NOCHECK CONSTRAINT ALL. ...
    (comp.databases.ms-sqlserver)
  • Re: SQL Server error when attempting to delete a record
    ... What version of SQL Server have you got? ... recordto be affected and execute this command based on the Primary Key ... Script the table's definition to a file (including constraints, indices, ... of the database, factors like that. ...
    (microsoft.public.sqlserver.server)
  • Re: ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
    ... help explain why I cannot delete and recreate constraints in a sp or script ... > Columnist, SQL Server Professional ... >> Toronto, ON Canada ...
    (microsoft.public.sqlserver.server)
Loading