一个表原来三个键联合做主键，现在要去掉一个，语句怎么写？

[我是丁学]

原来建表的语句：
CREATE TABLE [choosesub] (
[snumber] [char] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[sunumber] [char] (8) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[aaccount] [int] NOT NULL ,
CONSTRAINT [PK_CHOOSESUB] PRIMARY KEY CLUSTERED
(
[snumber],
[sunumber],
[aaccount]
) ON [PRIMARY] ,
CONSTRAINT [FK_CHOOSESU_CHOOSESUB_ADMIN] FOREIGN KEY
(
[aaccount]
) REFERENCES [admin] (
[aaccount]
),
CONSTRAINT [FK_CHOOSESU_CHOOSESUB_STUDENT] FOREIGN KEY
(
[snumber]
) REFERENCES [student] (
[snumber]
),
CONSTRAINT [FK_CHOOSESU_CHOOSESUB_SUBJECT] FOREIGN KEY
(
[sunumber]
) REFERENCES [subject] (
[sunumber]
)
) ON [PRIMARY]
GO


现在需要去掉[aaccount]的主键，只留另外两个做主键，并且要把[aaccount]改为可以为空，语句应该怎么写？谢谢

[duanduan1122]

1.删除主键
alter table choosesub
drop constraint PK_CHOOSESUB

2.重新建立主键
alter table choosesub
add constraint PK_CHOOSESUB primary key（[snumber],[sunumber]）

[duanduan1122]

其他几个例子：
C. 更改表以添加具有约束的列
下例向表中添加具有 UNIQUE 约束的新列。

CREATE TABLE doc_exc ( column_a INT)
GO
ALTER TABLE doc_exc ADD column_b VARCHAR(20) NULL
CONSTRAINT exb_unique UNIQUE
GO
EXEC sp_help doc_exc
GO
DROP TABLE doc_exc
GO

D. 更改表以添加未验证的约束
下例向表中的现有列上添加约束。该列中存在一个违反约束的值；因此，利用 WITH NOCHECK 来防止对现有行验证约束，从而允许该约束的添加。

CREATE TABLE doc_exd ( column_a INT)
GO
INSERT INTO doc_exd VALUES (-1)
GO
ALTER TABLE doc_exd WITH NOCHECK
ADD CONSTRAINT exd_check CHECK (column_a > 1)
GO
EXEC sp_help doc_exd
GO
DROP TABLE doc_exd
GO

E. 更改表以添加多个带有约束的列
下例向表中添加多个带有约束的新列。第一个新列具有 IDENTITY 属性；表中每一行的标识列都将具有递增的新值。

CREATE TABLE doc_exe ( column_a INT CONSTRAINT column_a_un UNIQUE)
GO
ALTER TABLE doc_exe ADD

/* Add a PRIMARY KEY identity column. */
column_b INT IDENTITY
CONSTRAINT column_b_pk PRIMARY KEY,

/* Add a column referencing another column in the same table. */
column_c INT NULL
CONSTRAINT column_c_fk
REFERENCES doc_exe(column_a),

/* Add a column with a constraint to enforce that */
/* nonnull data is in a valid phone number format. */
column_d VARCHAR(16) NULL
CONSTRAINT column_d_chk
CHECK
(column_d IS NULL OR
column_d LIKE "[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]" OR
column_d LIKE
"([0-9][0-9][0-9]) [0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]"),

/* Add a nonnull column with a default. */
column_e DECIMAL(3,3)
CONSTRAINT column_e_default
DEFAULT .081
GO
EXEC sp_help doc_exe
GO
DROP TABLE doc_exe
GO

[lilu207]

请直接搜索“主键”，有很多以前的老贴