MySQL之完整性约束
一 介绍
约束条件与数据类型的宽度一样，都是可选参数
作用：用于保证数据的完整性和一致性
主要分为：
PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK) 标识该字段为该表的外键
NOT NULL 标识该字段不能为空
UNIQUE KEY (UK) 标识该字段的值是唯一的
AUTO_INCREMENT 标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT 为该字段设置默认值
UNSIGNED 无符号
ZEROFILL 使用0填充
1 1. 是否允许为空，默认NULL，可设置NOT NULL，字段不允许为空，必须赋值 2 2. 字段是否有默认值，缺省的默认值是NULL，如果插入记录时不给字段赋值，此字段使用默认值 3 sex enum('male','female') not null default 'male' 4 age int unsigned NOT NULL default 20 必须为正值（无符号） 不允许为空 默认是20 5 3. 是否是key 6 主键 primary key 7 外键 foreign key 8 索引 (index,unique...)
2、not null与default
是否可空，null表示空，非字符串
not null - 不可空
null - 可空
==================not null==================== mysql> create table t1(id int); #id字段默认可以插入空 mysql> desc t1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ mysql> insert into t1 values(); #可以插入空 mysql> create table t2(id int not null); #设置字段id不为空 mysql> desc t2; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ mysql> insert into t2 values(); #不能插入空 ERROR 1364 (HY000): Field 'id' doesn't have a default value ==================default==================== #设置id字段有默认值后，则无论id字段是null还是not null，都可以插入空，插入空默认填入default指定的默认值 mysql> create table t3(id int default 1); mysql> alter table t3 modify id int not null default 1; ==================综合练习==================== mysql> create table student( -> name varchar(20) not null, -> age int(3) unsigned not null default 18, -> sex enum('male','female') default 'male', -> hobby set('play','study','read','music') default 'play,music' -> ); mysql> desc student; +-------+------------------------------------+------+-----+------------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------------------------+------+-----+------------+-------+ | name | varchar(20) | NO | | NULL | | | age | int(3) unsigned | NO | | 18 | | | sex | enum('male','female') | YES | | male | | | hobby | set('play','study','read','music') | YES | | play,music | | +-------+------------------------------------+------+-----+------------+-------+ mysql> insert into student(name) values('egon'); mysql> select * from student; +------+-----+------+------------+ | name | age | sex | hobby | +------+-----+------+------------+ | egon | 18 | male | play,music | +------+-----+------+------------+ 验证
3、unique
作用：限制字段的值唯一
#单列唯一 create table t1(id int nnique,name char(16)); #联合唯一 create table server(id int nuique,ip char(15),port int,unique(ip,port));
4、primary key
primary key 单从约束的角度去看，就等同于not null unique
强调;
1、一张表中必须有，并且只能有一个主键
2、一张表中都应该有一个id字段，而且应该把id字段做成主键
1 ============单列做主键=============== 2 #方法一：not null+unique 3 create table department1( 4 id int not null unique, #主键 5 name varchar(20) not null unique, 6 comment varchar(100) 7 ); 8 9 mysql> desc department1; 10 +---------+--------------+------+-----+---------+-------+ 11 | Field | Type | Null | Key | Default | Extra | 12 +---------+--------------+------+-----+---------+-------+ 13 | id | int(11) | NO | PRI | NULL | | 14 | name | varchar(20) | NO | UNI | NULL | | 15 | comment | varchar(100) | YES | | NULL | | 16 +---------+--------------+------+-----+---------+-------+ 17 rows in set (0.01 sec) 18 19 #方法二：在某一个字段后用primary key 20 create table department2( 21 id int primary key, #主键 22 name varchar(20), 23 comment varchar(100) 24 ); 25 26 mysql> desc department2; 27 +---------+--------------+------+-----+---------+-------+ 28 | Field | Type | Null | Key | Default | Extra | 29 +---------+--------------+------+-----+---------+-------+ 30 | id | int(11) | NO | PRI | NULL | | 31 | name | varchar(20) | YES | | NULL | | 32 | comment | varchar(100) | YES | | NULL | | 33 +---------+--------------+------+-----+---------+-------+ 34 rows in set (0.00 sec) 35 36 #方法三：在所有字段后单独定义primary key 37 create table department3( 38 id int, 39 name varchar(20), 40 comment varchar(100), 41 constraint pk_name primary key(id); #创建主键并为其命名pk_name 42 43 mysql> desc department3; 44 +---------+--------------+------+-----+---------+-------+ 45 | Field | Type | Null | Key | Default | Extra | 46 +---------+--------------+------+-----+---------+-------+ 47 | id | int(11) | NO | PRI | NULL | | 48 | name | varchar(20) | YES | | NULL | | 49 | comment | varchar(100) | YES | | NULL | | 50 +---------+--------------+------+-----+---------+-------+ 51 rows in set (0.01 sec) 52 53 单列主键
create table t19( ip char(15), port int, primary key(ip,port) );
5、auto_increment
束字段为自动增长，被约束的字段必须同时被key约束
create table t20( id int primary key auto_increment, name char(16) )engine=innodb; # auto_increment注意点： 1、通常与primary key连用，而且通常是给id字段加 2、auto_incremnt只能给被定义成key（unique key，primary key）的字段加
6、foreign key
就是表与表之间的某种约定的关系，由于这种关系的存在，能够让表与表之间的数据，更加的完整，关连性更强。
首先找出两张表之间的关系；
分析步骤：
1、先站在左表的角度去找
是否左表的多条记录对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表的一个字段（通常为id）
2、再站右表的角度去找
是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表的一个字段（id）
3、总结关系：
多对一：
如果只有步骤一成立，则左表多对一右表；
如果只有步骤二成立，则右表多对一左表；
多对多：
如果步骤1与步骤2都成立，则证明这两张表是一个双向的多对一（多对多），需要定义一个这两张表的关系表来存放二者的关系。
一对一：
如果步骤1 和步骤2都不成立，而是左表的一条记录只对应右表的一条记录，反之亦然，这种情况，就是在左表foreign key 右表的基础上，将左表的外键字段设置成unique即可。
如何实现：
在关联表中新增一个字段，该字段指向被关联表的id字段。
foreign key使用时注意：
1、在创建表时，先创建被关联的表，才能建关联表
2、在插入记录时，必须先插入被关联表，才能插入关联表。
3、删除时必须先删除被关联表。
3、表类型必须时innodb储存引擎，且被关联字段，即references指定的另外一个表的字段必须必须保证唯一
1 多对一： 2 创建被关联表： 3 create table dep( 4 id int primary key auto_increment, 5 dep_name char(16), 6 dep_comment char(60) 7 ); 8 9 创建关联表： 10 create table emp( 11 id int primary key aout_increment, 12 name char(16), 13 gender enum('male','female') not null default 'male', 14 dep_id int, 15 foreign key(dep_id) references dep(id) 16 on update cascade #同步更新 17 on delete cascade #同步删除 18 ); 19 20 mysql> desc emp; 21 +-------------+----------+------+-----+---------+----------------+ 22 | Field | Type | Null | Key | Default | Extra | 23 +-------------+----------+------+-----+---------+----------------+ 24 | id | int(11) | NO | PRI | NULL | auto_increment | 25 | dep_name | char(16) | YES | | NULL | | 26 | dep_comment | char(60) | YES | | NULL | | 27 +-------------+----------+------+-----+---------+----------------+ 28 3 rows in set (0.00 sec) 29 30 31 mysql> desc dep; 32 +--------+-----------------------+------+-----+---------+----------------+ 33 | Field | Type | Null | Key | Default | Extra | 34 +--------+-----------------------+------+-----+---------+----------------+ 35 | id | int(11) | NO | PRI | NULL | auto_increment | 36 | name | char(16) | YES | | NULL | | 37 | gender | enum('male','female') | NO | | male | | 38 | dep_id | int(11) | YES | MUL | NULL | | 39 +--------+-----------------------+------+-----+---------+----------------+ 40 4 rows in set (0.00 sec) 41 42 先向被关联表插入数据： 43 insert into dep(dep_name,dep_comment) values 44 ('教学部','辅导学生学习，教授课程'), 45 ('外交部','对外交流'), 46 ('技术部','提供技术支持'); 47 48 49 insert into emp(name,gender,dep_id) values 50 ('alex','male',1), 51 ('egon','male',2), 52 ('lxx','male',1), 53 ('wxx','male',1), 54 ('wz','female',3); 55 56 mysql> select *from dep; 57 +----+-----------+-----------------------------------+ 58 | id | dep_name | dep_comment | 59 +----+-----------+-----------------------------------+ 60 | 1 | 教学部 | 辅导学生学习，教授课程 | 61 | 2 | 外交部 | 对外交流 | 62 | 3 | 技术部 | 提供技术支持 | 63 +----+-----------+-----------------------------------+ 64 3 rows in set (0.00 sec) 65 66 mysql> select * from emp; 67 +----+------+--------+--------+ 68 | id | name | gender | dep_id | 69 +----+------+--------+--------+ 70 | 1 | alex | male | 1 | 71 | 2 | egon | male | 2 | 72 | 3 | lxx | male | 1 | 73 | 4 | wxx | male | 1 | 74 | 5 | wz | female | 3 | 75 +----+------+--------+--------+ 76 5 rows in set (0.00 sec) 77 78 同步更新、删除 79 mysql> select * from dep; 80 +-----+-----------+-----------------------------------+ 81 | id | dep_name | dep_comment | 82 +-----+-----------+-----------------------------------+ 83 | 2 | 外交部 | 对外交流 | 84 | 3 | 技术部 | 提供技术支持 | 85 | 100 | 教学部 | 辅导学生学习，教授课程 | 86 +-----+-----------+-----------------------------------+ 87 3 rows in set (0.00 sec) 88 89 mysql> 90 mysql> select * from emp; 91 +----+------+--------+--------+ 92 | id | name | gender | dep_id | 93 +----+------+--------+--------+ 94 | 1 | alex | male | 100 | 95 | 2 | egon | male | 2 | 96 | 3 | lxx | male | 100 | 97 | 4 | wxx | male | 100 | 98 | 5 | wz | female | 3 | 99 +----+------+--------+--------+ 100 5 rows in set (0.00 sec) 101 102 mysql> select *from dep; 103 +----+-----------+--------------------+ 104 | id | dep_name | dep_comment | 105 +----+-----------+--------------------+ 106 | 2 | 外交部 | 对外交流 | 107 | 3 | 技术部 | 提供技术支持 | 108 +----+-----------+--------------------+ 109 2 rows in set (0.00 sec) 110 111 mysql> select *from emp; 112 +----+------+--------+--------+ 113 | id | name | gender | dep_id | 114 +----+------+--------+--------+ 115 | 2 | egon | male | 2 | 116 | 5 | wz | female | 3 | 117 +----+------+--------+--------+ 118 2 rows in set (0.00 sec) 119 120 多对多： 121 create table author( 122 id int primary key auto_increement, 123 name char(16) 124 ); 125 126 chreate table book( 127 id int primary key auto_increment, 128 bname char(16), 129 price int 130 ); 131 132 133 insert into author(name) values 134 ('egon'), 135 ('alex'), 136 ('lxx'); 137 138 139 insert into book(bname,price) values 140 ('python从入门到入土',200), 141 ('葵花宝典切割到精通',800), 142 ('九阴真经',500), 143 ('九阳神功',100); 144 145 创建第三张表 146 147 create table author2book( 148 id int primary key auto_increment, 149 author_id int, 150 book_id int, 151 foreign key(author_id) references author(id) 152 on update cascade 153 on delete cascade, 154 foreign key(book_id) references book(id) 155 on update cascade 156 on delete cascade 157 ); 158 159 160 mysql> create table author2book( 161 -> id int primary key auto_increment, 162 -> author_id int, 163 -> book_id int, 164 -> foreign key(author_id) references author(id) 165 -> on update cascade 166 -> on delete cascade 167 -> foreign key(book_id) references book(id) 168 -> on update cascade 169 -> on delete cascade 170 -> ); 171 一对一： 172 173 create table customer( 174 id int primary key auto_increment, 175 name char(20) not null, 176 qq char(10) not null, 177 phone char(16) not null 178 ); 179 180 create table student( 181 id int primary key auto_increment, 182 class_name char(20) not null, 183 customer_id int unique, #该字段一定要是唯一的 184 foreign key(customer_id) references customer(id) #外键的字段一定要保证unique 185 on delete cascade 186 on update cascade 187 ); 188 189 insert into customer(name,qq,phone) values 190 ('李飞机','31811231',13811341220), 191 ('王大炮','123123123',15213146809), 192 ('守榴弹','283818181',1867141331), 193 ('吴坦克','283818181',1851143312), 194 ('赢火箭','888818181',1861243314), 195 ('战地雷','112312312',18811431230) 196 ; 197 198 199 #增加学生 200 insert into student(class_name,customer_id) values 201 ('脱产3班',3), 202 ('周末19期',4), 203 ('周末19期',5) 204 ;
浙公网安备 33010602011771号