如何在SQL Server 2008中创建复合主键

我想在SQL Server 2008中创建表，但我不知道如何创建复合主键。我怎样才能做到这一点？

382293 次浏览
小开 
create table my_table (
column_a integer not null,
column_b integer not null,
column_c varchar(50),
primary key (column_a, column_b)
);
小开 
CREATE TABLE UserGroup
(
[User_Id] INT NOT NULL,
[Group_Id] INT NOT NULL


CONSTRAINT PK_UserGroup PRIMARY KEY NONCLUSTERED ([User_Id], [Group_Id])
)
小开

Via Enterprise Manager (SSMS)...

  • Right Click on the Table you wish to create the composite key on and select Design.
  • Highlight the columns you wish to form as a composite key
  • Right Click over those columns and Set Primary Key

To see the SQL you can then right click on the Table > Script Table As > Create To

小开

First create the database and table, manually adding the columns. In which column to be primary key. You should right click this column and set primary key and set the seed value of the primary key.

小开

To create a composite unique key on table 

ALTER TABLE [TableName] ADD UNIQUE ([Column1], [Column2], [column3]);
小开

I know I'm late to this party, but for an existing table, try:

ALTER table TABLE_NAME
ADD CONSTRAINT [name of your PK, e.g. PK_TableName] PRIMARY KEY CLUSTERED (column1, column2, etc.)
小开 
CREATE TABLE UserGroup
(
[User_Id] INT Foreign Key,
[Group_Id] INT foreign key,


PRIMARY KEY ([User_Id], [Group_Id])
)
小开

For MSSQL Server 2012

CREATE TABLE usrgroup(
usr_id int FOREIGN KEY REFERENCES users(id),
grp_id int FOREIGN KEY REFERENCES groups(id),


PRIMARY KEY (usr_id, grp_id)
)

UPDATE

I should add !

If you want to add foreign / primary keys altering, firstly you should create the keys with constraints or you can not make changes. Like this below:

CREATE TABLE usrgroup(
usr_id int,
grp_id int,


CONSTRAINT FK_usrgroup_usrid FOREIGN KEY (usr_id) REFERENCES users(id),
CONSTRAINT FK_usrgroup_groupid FOREIGN KEY (grp_id) REFERENCES groups(id),


CONSTRAINT PK_usrgroup PRIMARY KEY (usr_id,grp_id)
)

Actually last way is healthier and serial. You can look the FK/PK Constraint names (dbo.dbname > Keys > ..) but if you do not use a constraint, MSSQL auto-creates random FK/PK names. You will need to look at every change (alter table) you need.

I recommend that you set a standard for yourself; the constraint should be defined according to the your standard. You will not have to memorize and you will not have to think too long. In short, you work faster.


提交答案