如何向表中添加复合主键

create table d(id numeric(1), code varchar(2))

After I create the above table how can I add a composite primary key on both fields and also a foreign key?

在创建上面的表之后，如何在两个字段和一个外键上添加复合主键?

5 个解决方案

#1

In Oracle, you could do this:

在Oracle中，您可以这样做:

create table D (
  ID numeric(1),
  CODE varchar(2),
  constraint PK_D primary key (ID, CODE)
);

#2

alter table d add constraint pkc_Name primary key (id, code)

修改表d添加约束pkc_Name主键(id、代码)

should do it. There's lots of options to a basic primary key/index depending on what DB your working with.

应该这样做。对于基本主键/索引有很多选项，这取决于您使用的是什么数据库。

#3

The ALTER TABLE statement presented by Chris should work, but first you need to declare the columns NOT NULL. All parts of a primary key need to be NOT NULL.

Chris提供的ALTER TABLE语句应该可以使用，但是首先需要声明列不是NULL。主键的所有部分都必须不为空。

#4

You don't need to create the table first and then add the keys in subsequent steps. You can add both primary key and foreign key while creating the table:

您不需要先创建表，然后在后续步骤中添加键。在创建表时，可以添加主键和外键:

This example assumes the existence of a table (Codes) that we would want to reference with our foreign key.

这个示例假设存在一个表(代码)，我们希望使用外键引用它。

CREATE TABLE d (
id [numeric](1),
code [varchar](2),
PRIMARY KEY (id, code),
CONSTRAINT fk_d_codes FOREIGN KEY (code) REFERENCES Codes (code)
)

If you don't have a table that we can reference, add one like this so that the example will work:

如果你没有我们可以参考的表，可以添加一个这样的表，这样这个示例就可以工作了:

CREATE TABLE Codes (
    Code [varchar](2) PRIMARY KEY   
    )

NOTE: you must have a table to reference before creating the foreign key.

注意:在创建外键之前必须有一个要引用的表。

#5

If using Sql Server Management Studio Designer just select both rows (Shift+Click) and Set Primary Key.

如果使用Sql Server Management Studio Designer，只需选择这两行(Shift+Click)并设置主键。

#1