Re: Changing a primary key data type

From: Dan Guzman (guzmanda_at_nospam-online.sbcglobal.net)
Date: 10/15/04 

Date: Fri, 15 Oct 2004 07:23:40 -0500

You can detailed table information, including constraints and indexes, with
sp_help. Then, use your ALTER TABLE to remove the desired constraint. For
example:

    EXEC sp_help 'MyTable'

    ALTER TABLE MyTable
    DROP CONSTRAINT <constraint name here>

Consider explicitly naming the constraint with you add it back. This will
make subsequent schema modifications easier:

ALTER TABLE MyTable
ADD CONSTRAINT PK_MyTable
PRIMARY KEY (MyColumn) 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter B.L. Rasmussen" <PeterBLRasmussen@discussions.microsoft.com> wrote in 
message news:8874FCA5-2FBE-4E0F-BEC9-38417C15C238@microsoft.com...
>I need to change the data type for a column that is part of the primary key
> of the table from int to nvarchar(15). I will have to get rid of the 
> primary
> key constraint first, bu I can't seem to figure out how to do it.
>
> The constraint (or whatever it is) was originally created by Microsoft
> Access Upgrading Wizard, so I don't know what it has made.
>
> I know that the name of the object is "aaaaaVarer_PK"
>
> if I try to drop constraint:
> alter table Varer drop constraint aaaaaVarer_PK
> I get: 'aaaaaVarer_PK' is not a constraint.
>
> if I try to drop index:
> DROP INDEX Varer.aaaaaVarer_PK
> I get: An explicit DROP INDEX is not allowed on index
> 'Varer.aaaaaVarer_PK'. It is being used for PRIMARY KEY constraint
> enforcement.
>
> There must exist a PK constraint related to this index. Where do I find 
> the
> name of that constraint?
>
> in sysobjects aaaaaVarer_PK has xtype=PK, type=K, id=18099105
>
> id 18099105 exists in sysconstraints, but all I can see there is that it 
> is
> related to the Varer table
>

Relevant Pages

  • SQL Server confused about primary keys. So am I.
    ... We are generating a script to convert a client's database to Sql ... ALTER TABLE dbo.Categories ADD ... CONSTRAINT PK_Categories PRIMARY KEY CLUSTERED ...
    (microsoft.public.sqlserver.server)
  • Re: Alter table help
    ... alter table table_name add constraint constraint_name primary key ... > "Tom Pennington" wrote in message ... >> Okay, I'm trying to modify a tables primary key, actually, I'm trying to ...
    (microsoft.public.sqlserver.programming)
  • Re: Drop Primary Key with SQL/VBA
    ... >> Syntax error in ALTER TABLE statement. ... > Do you want to drop the Primary Key constraint on a field..... ... > CREATE TABLE tblShipping ...
    (microsoft.public.access.queries)
  • Re: Database design question
    ... say we have document and we need to specify permissions to the ... Is there anyway we can have constraint like ... alter table DocumentPermission ... You also need a primary key for DocumentPermission. ...
    (comp.databases.theory)
  • Re: Is this legal SQL ? Left join dbo.table on filed1=filed2 and filed3 = @memvar
    ... [atbID] ... ALTER TABLE.WITH NOCHECK ADD ... CONSTRAINT PRIMARY KEY CLUSTERED ... CONSTRAINT FOREIGN KEY ...
    (microsoft.public.sqlserver.programming)