MySQL名称的主键约束 [英] MySql primary key constraint with name

问题描述

数据定义语句:

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)
)

CONSTRAINT pk_PersonID PRIMARY KEY (P_Id)?

与此相反

PRIMARY KEY (P_Id)?

除了此.

推荐答案

与MySQL忽略CONSTRAINT pk_PersonID部分一样.您可以通过创建表然后将其转储或发出SHOW CREATE TABLE Persons进行检查.

It's the same as MySQL ignores the CONSTRAINT pk_PersonID part. You can check by creating the table and then dumping it or issuing SHOW CREATE TABLE Persons.

我猜想它仅支持与其他SQL Server兼容的这种语法(但对于主键和其他本地键则忽略它)，并且不存储其信息(约束名称).

I guess it supports this syntax only for compatibility with other SQL servers (but ignores it for primary and other local keys) and does not store its information (the constraint name).

但是，与外键配合使用时，CONSTRAINT关键字也用于MySQL.

However for usage with foreign keys the CONSTRAINT keyword is used also in MySQL.

mysql> CREATE TABLE test.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)
    -> );
Query OK, 0 rows affected (0.50 sec)

server$ mysqldump -p test Persons
Enter password:
--
-- Table structure for table `Persons`
--
DROP TABLE IF EXISTS `Persons`;
CREATE TABLE `Persons` (
  `P_Id` int(11) NOT NULL,
  `LastName` varchar(255) NOT NULL,
  `FirstName` varchar(255) DEFAULT NULL,
  `Address` varchar(255) DEFAULT NULL,
  `City` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`P_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

这里还进行了测试，以证明MySQL不会在任何地方存储约束名称，并且在打印错误时也不会使用约束名称(如针对其他有问题的SQL Server所述

Here is also test to prove MySQL doesn't store the constraint name anywhere and doesn't use it when printing errors (as mentioned for other SQL servers in question What is the purpose of constraint naming :

mysql> insert into Persons (P_Id) values(1);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into Persons (P_Id) values(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

这篇关于MySQL名称的主键约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！