主键约束,其特点是:
不允许表中有NULL记录;
不允许表中有重复记录;
每张数据表只能存在一个PRIMARY KEY约束;
创建主键约束后,系统将自动创建主键索引(是一种特殊的唯一索引);
-- 创建数据表时添加列级主键约束 CREATE TABLE t4( id SMALLINT UNSIGNED PRIMARY KEY, username VARCHAR(20) ); DESC t4; INSERT t4(id,username) VALUES(1,'Tom');
-- 创建数据表时添加表级主键约束 CREATE TABLE t5( first_name VARCHAR(10), lASt_name VARCHAR(20), PRIMARY KEY (first_name,lASt_name) ); DESC t5;
Field | Type | NULL | KEY | DEFAULT | Extra |
---|---|---|---|---|---|
first_name | VARCHAR(10) | NO | PRI | NULL | |
last_name | VARCHAR(20) | NO | PRI | NULL | |
age | tinyINT(3) unsigned | YES | NULL | ||
---- |
CREATE TABLE t6( first_name VARCHAR(10), last_name VARCHAR(20), age TINYINT UNSIGNED, PRIMARY KEY (first_name,lASt_name) );
插入记录时可能产生的错误
ERROR 1364 (HY000): Field 'id' doesn't have a DEFAULT value 错误原因:插入记录时,没有为禁止为空且没有赋认值的字段进行赋值;
ERROR 1062 (23000): Duplicate entry '1' fOR KEY 'PRIMARY' 错误原因:因主键值重复而导致插入/更新记录失败;
-- 创建表时,添加主键约束 CREATE TABLE word01( id INT PRIMARY KEY, name VARCHAR(30), age INT(3) ); DESC word01; INSERT INTo word01 VALUES(101,'张三',20); INSERT INTo word01 VALUES(101,'李四',30); --错误 INSERT INTo word01(name,age) VALUES('王五',35); --错误
使用主键约束来修饰两列或者多列
CREATE TABLE word02( id INT, name VARCHAR(30), age INT(3), PRIMARY KEY(id,name) ); DESC word02;
-- 创建一张表,表名为word03,表中包含的字段有id INT(8),name VARCHAR(30),sex CHAR(3),age INT(3) 修改表时,对id列添加主键约束 CREATE TABLE word03( id INT(8), name VARCHAR(30), sex CHAR(3), age INT(3) ); DESC word03; --修改前 ALTER TABLE word03 ADD PRIMARY KEY(id); DESC word03; --修改后
-- 创建一张表,表名为word04,表中包含的字段有wid INT,wname VARCHAR(30),age INT(3),修改表时,对wid和wname列添加主键约束并验证 CREATE TABLE word04( wid INT, wname VARCHAR(30), age INT(3) ); DESC word04; ALTER TABLE word04 ADD PRIMARY KEY(wid,wname); DESC word04;
语法格式:
ALTER TABLE 表名 DROP PRIMARY KEY;
-- 删除word01表中主键约束并验证 ALTER TABLE word01 DROP PRIMARY KEY; DESC word01;