mysql数据表约束

mysql数据表的约束
1.使用主键约束
　　主键又称主码，是表中一列或多列的组合，主键约束（Primary Key Constraint）要求主键列的数据唯一，并且不允许为空，主键能够唯一标识表中的一条记录，可以结合外键来定义不同数据表之间的关系，并且可以加快数据库查询的速度。

主键两种类型：单字段主键和多字段联合主键
　　(1)单字段
　　两种语法：
　　a.定义列的同时指定主键：字段名 数据类型 primary key [默认值]

create table tb_name(
id int(11) primary key,
name char(25),
age int(3),
deptID int(11)
) engine=innodb default charset utf8;

　　b.定义完所有列之后指定主键： [constraint <约束名> primary key [字段名]]

create table tb_name(
id int(11),
name char(25),
age int(3),
deptID int(11),
primary key (id)
) engine=innodb default charset utf8;

　　

　　

　　(2)多字段联合主键
　　定义万所有列之后指定：
　　primary key (字段1，字段2...字段n)

create table tb_name1(
id int(11),
name char(25),
age int(3),
deptID int(11),
primary key (id,deptID)
) engine=innodb default charset utf8;

　　

2.使用外键约束
　　外键用来在两个表的数据之间建立链接，它可以是一列或者多列，一个表可以有一个或多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键的某个值。
　　外键：首先它是表中的一个字段，它可以不是本表的主键，但对应另外一个表的主键，外键的主要作用是保证数据引用的完整性，定义外键后，不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据的一致性、完整性。

　　如：部门表tb_dept的主键是id,在员工表的tb_emp中有一个键deptId与这个id关联。

　　主表(父表)：对应两个具有关联关系的表而言，相关联字段中主键所在的那个表即是主表。
　　从表(子表)：对应两个具有关联关系的表而言，相关联字段中外键所在的那个表即是从表。

　　创建外键的语法：

constraint 外键名 foreign key (字段名[,字段2...]) references 主表名(主键列1 [,主键列2...])
constraint 外键名 foreign key (本数据表字段名[,字段2...]) references 另外一个数据表名(主键列1 [,主键列2...])

　　实例1-一对一：创建班级和学生表

create table class(
cid int(11) auto_increment primary key,
caption char(12) not null
) engine=innodb default charset utf8;

create table student(
sid int(11) not null auto_increment primary key,
sname char(12) not null,
gender char(3) not null,
class_id int(11),
constraint fk_student_class foreign key (class_id) references class(cid)
) engine=innodb default charset utf8;

　　实例2-一对多：

用户表和部门表

用户：
    1   felix   1
    2   alina   2
    3   allen   3

部门表：
    1   IT
    2   CSO
    3   MT

create table user(
    id int(10) auto_increment primary key,
    sname char(25) not null,
    gender char(10) not null
    ) engine=innodb default charset utf8;

insert into user(sname,gender) values ('felix','男');
insert into user(sname,gender) values ('mission','女');
insert into user(sname,gender) values ('allen','女');
insert into user(sname,gender) values ('candy','女');
insert into user(sname,gender) values ('alina','女');

create table log(
    id int(10) auto_increment primary key,
    mname int(10) not null,
    fname int(10) not null,
    constraint fk_user_log1 foreign key (mname) references user (id),
    constraint fk_user_log foreign key (fname) references user (id)
    ) engine=innodb default charset utf8;

insert into log(mname,fname) values ('1','3');
insert into log(mname,fname) values ('1','4');
insert into log(mname,fname) values ('3','4');
insert into log(mname,fname) values ('2','3');
insert into log(mname,fname) values ('2','5');

　　

　　实例3-多对多：

 用户表
 主机表
  用户主机关系表
create table user(
    id int(10) auto_increment primary key,
    sname char(25) not null,
    gender char(10) not null
    ) engine=innodb default charset utf8;

create table host(
    id int(10) auto_increment primary key,
    host char(25) not null
    ) engine=innodb default charset utf8;

insert into host(host) values ('cchncqsrvfs01');
insert into host(host) values ('cchncqsrvfs02');
insert into host(host) values ('cchncqsrvfs03');


create table useinfo(
    id int(10) auto_increment primary key,
    nameid int(10) not null,
    hostid int(10) not null,
    unique uq_name_host (nameid,hostid),
    constraint fk_userinfo_user foreign key (nameid) references user (id),
    constraint fk_userinfo_host foreign key (hostid) references host (id)
    ) engine=innodb default charset utf8;

　　

3.使用非空约束
　　非空约束(Not Null Constraint) 指字段的值不能为空，对于使用了非空约束的字典，如果用户在增加数据时没有指定值，数据库系统就会报错。

　　非空约束语法：
　　字段名 数据类型 not null

create table class(
cid int(11) auto_increment primary key,
caption char(12) not null
) engine=innodb default charset utf8;

　　

4.使用唯一性约束

　　唯一性约束(Unique Constraint)要求该列唯一，允许为空，但只能出现一个空值，唯一约束可以确保一列或者几列不出现重复值。
　　唯一性约束语法：
　　字段名 数据类型 unique

create table class(
cid int(11) auto_increment primary key,
caption char(12) not null unique
) engine=innodb default charset utf8;

　　

　　unique和primary key 的区别：一个表中可以有多个字段声明为unique,但只能有一个primary key声明，声明为primary key的列不允许空值，但是声明为unique的字段允许空值的存在。

5.使用默认约束
　　默认约束(Default Constraint)指定某列的默认值。如果男性同学较多，性别就可以默认为'男'，如果插入一条新的记录时没有为这个字段赋值，那么系统就自动为这个字段赋值为'男'。

　　默认约束语法：
　　字段名 数据类型 default 默认值

create table student1(
sid int not null auto_increment primary key,
sname char(12),
gender char(3) default 'boy',
class_id int
) engine=innodb default charset utf8;

　　

6.设置表的属性值自动增加

　　在数据库应用中，经常希望在每次插入新记录时，系统自动生成字段的主键值，可以通过为表的主键增加 auto_increment 关键字来实现。
默认的在mysql中auto_increment的初始值为1。一个表中只能有一个字段使用auto_increment约束，且该字典必须为主键的一部分。

　　约束字段可以是任何整数类型(tinyint,smallint,int,bigint)。

　　自动增加语法：
   字段名 数据类型 auto_increment

create table student1(
    sid int not null auto_increment primary key,
    sname char(12),
    gender char(3) default 'boy',
    class_id int
    ) engine=innodb default charset utf8;
 

　　通过show create tables 表名 G; 查看创建表的命令和数据引擎、自增的值等，可以修改下次插入数据的自增值，

　　alter table 表名 auto_increment=20;

mysql> show create table student G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` char(12) DEFAULT NULL,
  `gender` char(3) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_student_class` (`class_id`),
  CONSTRAINT `fk_student_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`ci
d`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> alter table student auto_increment=20;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table student G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` char(12) DEFAULT NULL,
  `gender` char(3) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_student_class` (`class_id`),
  CONSTRAINT `fk_student_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`ci
d`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

　　修改已存在表名的默认值：（下一条记录的ID号）

desc 表名;
show create 表名 G；
alter table 表名 auto_increment=20;  修改自增的ID，即下一条数据的ID 从20开始

mysql> show create table student G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` char(12) DEFAULT NULL,
  `gender` char(3) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_student_class` (`class_id`),
  CONSTRAINT `fk_student_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`ci
d`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table student auto_increment=20;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table student G;
*************************** 1. row ***************************
       Table: student
Create Table: CREATE TABLE `student` (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `sname` char(12) DEFAULT NULL,
  `gender` char(3) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`sid`),
  KEY `fk_student_class` (`class_id`),
  CONSTRAINT `fk_student_class` FOREIGN KEY (`class_id`) REFERENCES `class` (`ci
d`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

　　设置自增的步长：

基于会话级别：
    show session variables like 'auto_inc%'; 查看全局变量
    set session auto_increment_increment=2; 设置会话级别 步长（关闭当前连接的客户端或者重新开的客户端步长还是1）
    set session auto_increment_offset=10;  设置起始值
基于全局级别：
    show global variables like 'auto_inc%'; 查看全局变量
    set global auto_increment_increment=2; 设置会话级别 步长（关闭当前连接的客户端或者重新开的客户端步长还是1）
    set global auto_increment_offset=10; 设置起始值

　　

mysql>  show session variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> set session auto_increment_increment=2;
Query OK, 0 rows affected (0.00 sec)

mysql>  show session variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 2     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
2 rows in set, 1 warning (0.01 sec)

mysql> set session auto_increment_offset=10;
Query OK, 0 rows affected (0.00 sec)

mysql>  show session variables like 'auto_inc%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 2     |
| auto_increment_offset    | 10    |
+--------------------------+-------+
2 rows in set, 1 warning (0.01 sec)