向现有表添加主键

I have an existing table called Persion. In this table I have 5 columns:

我有一个名为Persion的现有表。在本表中，我有5列:

• persionId
• persionId
• Pname
• Pname
• PMid
• PMid
• Pdescription
• Pdescription
• Pamt
• Pamt

When I created this table, I set PersionId and Pname as the primary key.

创建此表时，我将PersionId和Pname设置为主键。

I now want to include one more column in the primary key - PMID. How can I write an ALTER statement to do this? (I already have 1000 records in the table)

现在我想在主键- PMID中再包含一列。我该如何写一个修改语句来实现这一点呢?(表中已经有1000条记录)

9 个解决方案

#1

drop constraint and recreate it

删除约束并重新创建它

alter table Persion drop CONSTRAINT <constraint_name>

alter table Persion add primary key (persionId,Pname,PMID)

edit:

编辑:

you can find the constraint name by using the query below:

您可以使用下面的查询找到约束名称:

select OBJECT_NAME(OBJECT_ID) AS NameofConstraint
FROM sys.objects
where OBJECT_NAME(parent_object_id)='Persion'
and type_desc LIKE '%CONSTRAINT'

#2

I think something like this should work

我觉得像这样的东西应该有用

-- drop current primary key constraint
ALTER TABLE dbo.persion 
DROP CONSTRAINT PK_persionId;
GO

-- add new auto incremented field
ALTER TABLE dbo.persion 
ADD pmid BIGINT IDENTITY;
GO

-- create new primary key constraint
ALTER TABLE dbo.persion 
ADD CONSTRAINT PK_persionId PRIMARY KEY NONCLUSTERED (pmid, persionId);
GO

#3

-- create new primary key constraint
ALTER TABLE dbo.persion 
ADD CONSTRAINT PK_persionId PRIMARY KEY NONCLUSTERED (pmid, persionId);

is a better solution because you have control over the naming of the primary_key.

是更好的解决方案，因为您可以控制primary_key的命名。

---

It's better than just using

这比仅仅使用要好

ALTER TABLE Persion ADD PRIMARY KEY(persionId,Pname,PMID)

which yeilds randomized names and can cause problems when scripting out or comparing databases

哪些yeilds随机名称会在编写脚本或比较数据库时产生问题

#4

If you add primary key constraint

如果您添加主键约束

ALTER TABLE <TABLE NAME> ADD CONSTRAINT <CONSTRAINT NAME> PRIMARY KEY <COLUMNNAME>  

for example:

例如:

ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO)

#5

The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values and column cannot contain NULL Values.

主键约束惟一地标识数据库表中的每个记录。主键必须包含唯一值，列不能包含空值。

  -- DROP current primary key 
  ALTER TABLE tblPersons DROP CONSTRAINT <constraint_name>
  Example:
  ALTER TABLE tblPersons 
  DROP CONSTRAINT P_Id;


  -- ALTER TABLE tblpersion
  ALTER TABLE tblpersion add primary key (P_Id,LastName)

#6

Necromancing.
Just in case anybody has as good a schema to work with as me...
Here is how to do it correctly:

Necromancing。以防有人有和我一样好的模式……以下是正确的做法:

In this example, the table name is dbo.T_SYS_Language_Forms, and the column name is LANG_UID

在本例中，表名是dbo。T_SYS_Language_Forms，列名为LANG_UID

-- First, chech if the table exists...
IF 0 < (
    SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_TYPE = 'BASE TABLE'
    AND TABLE_SCHEMA = 'dbo'
    AND TABLE_NAME = 'T_SYS_Language_Forms'
)
BEGIN
    -- Check for NULL values in the primary-key column
    IF 0 = (SELECT COUNT(*) FROM T_SYS_Language_Forms WHERE LANG_UID IS NULL)
    BEGIN
        ALTER TABLE T_SYS_Language_Forms ALTER COLUMN LANG_UID uniqueidentifier NOT NULL 

        -- No, don't drop, FK references might already exist...
        -- Drop PK if exists (it is very possible it does not have the name you think it has...)
        -- ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT pk_constraint_name 
        --DECLARE @pkDropCommand nvarchar(1000) 
        --SET @pkDropCommand = N'ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT ' + QUOTENAME((SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
        --WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' 
        --AND TABLE_SCHEMA = 'dbo' 
        --AND TABLE_NAME = 'T_SYS_Language_Forms' 
        ----AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms' 
        --))
        ---- PRINT @pkDropCommand 
        --EXECUTE(@pkDropCommand) 
        -- Instead do
        -- EXEC sp_rename 'dbo.T_SYS_Language_Forms.PK_T_SYS_Language_Forms1234565', 'PK_T_SYS_Language_Forms';

        -- Check if they keys are unique (it is very possible they might not be)        
        IF 1 >= (SELECT TOP 1 COUNT(*) AS cnt FROM T_SYS_Language_Forms GROUP BY LANG_UID ORDER BY cnt DESC)
        BEGIN

            -- If no Primary key for this table
            IF 0 =  
            (
                SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
                WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' 
                AND TABLE_SCHEMA = 'dbo' 
                AND TABLE_NAME = 'T_SYS_Language_Forms' 
                -- AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms' 
            )
                ALTER TABLE T_SYS_Language_Forms ADD CONSTRAINT PK_T_SYS_Language_Forms PRIMARY KEY CLUSTERED (LANG_UID ASC)
            ;

        END -- End uniqueness check
        ELSE
            PRINT 'FSCK, this column has duplicate keys, and can thus not be changed to primary key...' 
    END -- End NULL check
    ELSE
        PRINT 'FSCK, need to figure out how to update NULL value(s)...' 
END 

#7

Try using this code:

尝试使用这段代码:

ALTER TABLE `table name` 
    CHANGE COLUMN `column name` `column name` datatype NOT NULL, 
    ADD PRIMARY KEY (`column name`) ;

#8

ALTER TABLE TABLE_NAME ADD PRIMARY KEY(`persionId`,`Pname`,`PMID`)

#9

Please try this-

请试试这个-

ALTER TABLE TABLE_NAME DROP INDEX `PRIMARY`, ADD PRIMARY KEY (COLUMN1, COLUMN2,..);