MySQL 一个表可以有多个主键吗？

Can a table have multiple Primary keys?

提问by Pankaj Kumar

I am very confused right now, maybe you can help me to understand the problem better regarding the question that can a table have two primary keys if yes then how? and it no then why?

我现在很困惑，也许你可以帮助我更好地理解这个问题，如果是的话，一个表可以有两个主键，那么如何？它没有那为什么？

回答by Diwakar

You ask if you can have more than one primary key fieldand you most certainly can. You can have only one primary key, but that can consist of as many columns as you need to uniquely identify your rows.

你问你是否可以有多个主键字段，你肯定可以。您只能有一个主键，但它可以包含任意数量的列，以唯一标识您的行。

Use something like this when you are creating your table:

创建表时，请使用以下内容：

CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) 

where P_Tdand LastNameare columns in your table.

表中的列在哪里P_Td和LastName是列。

If you think you want more than one primary key, then the answer is "not really." You can have only one primary key. However, you can have as many indexes as you want that have a unique constraint on them. A unique index does pretty much the same thing as a primary key.

如果您认为您需要多个主键，那么答案是“不是真的”。您只能有一个主键。但是，您可以拥有任意数量的索引，这些索引对它们具有唯一约束。唯一索引与主键的作用几乎相同。

for example :-

例如 ：-

CREATE TABLE Persons
(
   P_Id int NOT NULL,
   LastName varchar(255) NOT NULL,
   FirstName varchar(255),
   Address varchar(255),
   City varchar(255),
   CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
)

Note: In the example above there is only ONE PRIMARY KEY (pk_PersonID). However, the value of the pk_PersonIDis made up of two columns (P_Idand LastName).

注意：在上面的示例中，只有一个 PRIMARY KEY ( pk_PersonID)。但是， 的值pk_PersonID由两列 (P_Id和LastName) 组成。

回答by zzlalani

No You cannot have two primary keys in one table, but you can have composite primary key

否 一张表中不能有两个主键，但可以有复合主键

Because Primary key is an identity to the row and there can't be two IDs against a row.

因为主键是行的标识，并且一行不能有两个 ID。

回答by nvogel

A table can have many keys but by convention only one key per table is designated a "primary" one. Typically this may be the key deemed to be the "preferred" identifier for the information in that table or it may be a key singled out for some other specific purpose by the table's designer.

一张表可以有多个键，但按照惯例，每个表只有一个键被指定为“主”键。通常，这可能是被视为该表中信息的“首选”标识符的键，或者它可能是表设计者为某些其他特定目的而挑选出来的键。

In principle whatever function or property you associate with the key designated "primary" could just as well be associated with any other key as well. Therefore for many practical purposes you could designate more than one such "primary" key if you so choose - but only if the limitations of any particular DBMS permit.

原则上，您与指定为“主要”的键关联的任何功能或属性也可以与任何其他键关联。因此，出于许多实际目的，如果您愿意，您可以指定多个这样的“主”键——但前提是任何特定 DBMS 的限制都允许。

Under the relational model of data all keys are equal and there is no special function given to primary keys (in fact the relational usage of the term primary keyoriginally referred to anyand allkeys of a relation and not just one key). Unfortunately many DBMSs don't respect this principle and may limit certain features to one and only one key of a table, making it necessary to be selective about which key gets designated as primary. So the answer to your question ought to be YES in principle. When you need to achieve it in some particular SQL DBMS the actual answer is: it depends.

在数据的关系模型下，所有键都是相等的，并且没有赋予主键特殊功能（实际上，术语主键的关系用法最初是指关系的任何和所有键，而不仅仅是一个键）。不幸的是，许多 DBMS 不遵守这一原则，并且可能会将某些功能限制为一个表的一个且仅一个键，因此必须选择将哪个键指定为主键。所以你的问题的答案原则上应该是肯定的。当您需要在某些特定的 SQL DBMS 中实现它时，实际的答案是：这取决于。

回答by Andrew

You can only have 1 primary key - the range of keys that could all potentially be the primary key can be referred to as candidate keys. The one you select is the primary key, the other alternative keys can be implemented as unique constraints / indexes.

您只能有 1 个主键 - 可能都是主键的键范围可以称为候选键。您选择的一个是主键，其他备用键可以实现为唯一约束/索引。

So whilst there is only 1 primary key, you can still ensure primality of other fields / combination of fields using the unique constraint / index.

因此，虽然只有 1 个主键，但您仍然可以使用唯一约束/索引确保其他字段/字段组合的主要性。

回答by Bha15

No.The table have only on primary key. But that primary key can contain multiple field. Means when you create table and when you mention primary key, you can add more then one column which you want to. for example

不。该表只有主键。但是该主键可以包含多个字段。意味着当您创建表并提到主键时，您可以添加多于一列。例如

CREATE TABLE table_name ( col1 Datatype , col2 Datatype,col3 Datatype, col4 Datatype, PRIMARY KEY (col1,col2,col3) )

CREATE TABLE table_name ( col1 Datatype , col2 Datatype,col3 Datatype, col4 Datatype, PRIMARY KEY (col1,col2,col3) )

By this way you can add primary key in single table

通过这种方式，您可以在单个表中添加主键

回答by Arm Avi

You Can try FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

您可以尝试 FOREIGN KEY 是一个表中的一个字段（或字段集合），它引用另一个表中的 PRIMARY KEY。

回答by Niels Keurentjes

On a table you can make indexes, which allow the internal database engine to process the contents of the affected columns (1 to many) for easy lookup. Because the engine is at that point already evaluating and sorting the contents of the fields, it can also easily ensure uniqueness of the values. Thus an indexcan span 1 to many rows, and optionally also be unique.

您可以在表上创建索引，这允许内部数据库引擎处理受影响列的内容（一对多）以便于查找。因为引擎此时已经在评估和排序字段的内容，所以它也可以轻松确保值的唯一性。因此，索引可以跨越 1 到多行，也可以选择是唯一的。

A primary keyis a theoretically optional, though in practice mandatory, marker on a specific index that it's the eternally unique way of referencing a specific row in the table. It's usually either a GUID or an auto-increment integer (identityin SQL Server). The primary key itself is unique for any given table, and enforces a unique constraint by definition, but can optionally span multiple rows (a spannedindex/key).

一个主键是一个理论上可选的，但是在一个特定的指数，它是在引用表格中的特定行的永恒独特的方式实践强制性标记。它通常是 GUID 或自动递增的整数（identity在 SQL Server 中）。主键本身对于任何给定的表都是唯一的，并根据定义强制执行唯一约束，但可以选择跨越多行（跨越索引/键）。

You could for example have a junction table containing only 2 fields, which are both foreign keys, and together form the primary key/index of the table.

例如，您可以有一个仅包含 2 个字段的联结表，这两个字段都是外键，共同构成表的主键/索引。

回答by Nagaraj S

As you cannot define more than one column as Primary Key as below

由于您不能定义多于一列作为主键，如下所示

create table test1 (col1 numeric(10) primary key, col2 numeric(10) primary key 
,col3 numeric(10) primary key, col4 numeric(10)) 

It needs to be a composite key. Yes, we can have more than one column as primary key to solve some business requirements. Primary Keys ensures that the column(s) will not have duplicate values , Null in the table.

它需要是一个复合键。是的，我们可以有多个列作为主键来解决一些业务需求。主键确保列不会有重复的值，表中为空。

Below is the SQL to create a table with Composite Primary Key

下面是使用复合主键创建表的 SQL

CREATE TABLE track( 
col1 numeric(10) , col2 numeric(10) ,col3 numeric(10) primary key, col4 numeric(10), 
PRIMARY KEY (col1,col2,col3) 
)

回答by harish

CREATE TABLE track( col1 numeric(10) , col2 numeric(10) ,col3 numeric(10) , col4 numeric(10), PRIMARY KEY (col1,col2,col3) )

创建表轨道( col1 numeric(10) , col2 numeric(10) ,col3 numeric(10) , col4 numeric(10), PRIMARY KEY (col1,col2,col3) )

回答by Vaibhav Parmar

No.You cannot use more than 1 primary key in the table.for that you have composite key which is combination of multiple fields.

不可以。您不能在表中使用超过 1 个主键。因为您有多个字段组合的复合键。