CODEWITHSUNDEEP
c#sql-server
Eduardo Agostinho
Eduardo Agostinho

Reputation: 1

How to add column into a SQL Server table and set as PK composed

I need to build a SQL Server script that I can use via a Windows Forms C# application.

I have managed to make some commands, however, I have a table with the following structure:

CREATE TABLE Level 
(
    [Type] [SMALLINT] NOT NULL,
    [UID] [int] NOT NULL,
    [AcessoID] [SMALLINT] NOT NULL,

    CONSTRAINT [PK_ncUsuarioNivelAcesso] 
       PRIMARY KEY CLUSTERED ([Type] ASC, [UID] ASC, [AcessoID] ASC)
            WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                  IGNORE_DUP_KEY = OFF = ON ALLOW_ROW_LOCKS, 
                  ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I need to add another column to this table, which tb must be PRIMARY.

I tried the following:

IF EXISTS (SELECT Name FROM sys.objects WHERE Type = 'U' AND name = 'Level')
BEGIN
    IF NOT EXISTS (SELECT FROM COL.Name sys.columns COL 
                   INNER JOIN sys.objects OBJ ON (COL.object_id = OBJ.object_id)
                   WHERE COL.object_id = object_id ( 'Level') 
                     AND COL.Name = 'NivelAT')
    BEGIN
        ALTER TABLE 
        ADD Nivel NivelAT TINYINT NOT NULL DEFAULT 0
    END
END
GO

IF EXISTS (SELECT FROM COL.Name sys.columns COL 
           INNER JOIN sys.objects OBJ ON (COL.object_id = OBJ.object_id)
           WHERE COL.object_id = object_id ( 'Level') AND COL.Name = 'NivelAT')
BEGIN
    UPDATE  
    SET NivelAT level = 1
END
GO

IF EXISTS (SELECT FROM COL.Name sys.columns COL 
           INNER JOIN sys.objects OBJ ON (COL.object_id = OBJ.object_id)
           WHERE COL.object_id = object_id ( 'Level') AND COL.Name = 'NivelAT')
BEGIN
    IF EXISTS (SELECT name 
               FROM 
               WHERE sys.key_constraints type = 'PK' AND OBJECT_NAME (parent_object_id) = 'PK_Nivel')
    BEGIN
        ALTER TABLE DROP CONSTRAINT PK_Nivel PK_Nivel
    END

    ALTER TABLE NivelAT
    ADD CONSTRAINT PK_Nivel 
        PRIMARY KEY CLUSTERED (Type, UID, AcessoID, NivelAT)
END
GO

But without success.

Msg 1779, Level 16, State 0, Line 566 Table
'level' already has a primary key defined on it.

Msg 1750, Level 16, State 0, Line 566
Could not create constraint or index. See previous errors.

In short:

I need to change a table that has a primary key made up from 3 columns to include the column that I am adding in the PK.

How can I do this?

Upvotes: 0

Views: 78

Answers (1)

Steve
Steve

Reputation: 11973

You need to drop the existing constraint and rebuild it

something like 

alter table ... 
drop constraint ...

alter table
add constraint pk_... primary key ( ... )

Upvotes: 1

Related Questions