mysql外键是pk吗_mysql之外键

完整性约束

概念：约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证表与表之间的数据的完整性和准确性

可分为以下几种：

PRIMARY KEY (PK) 标识该字段为该表的主键，可以唯一的标识记录

FOREIGN KEY (FK) 标识该字段为该表的外键

UNIQUE KEY (UK) 标识该字段的值是唯一的

NOT NULL 标识该字段不能为空

AUTO_INCREMENT 标识该字段的值自动增长(整数类型，而且为主键)

DEFAULT 为该字段设置默认值

PRIMARY KEY

primary key字段的值不为空且唯一

1单列做主键

方法一： not null+unique

create table department1(

id int not null unique, #主键

name varchar(20) not null unique,

comment varchar(100)

);

方法二： 在某一个字段后用primary key (推荐)

create table department2(

id int primary key, #主键

name varchar(20),

comment varchar(100)

);

方法三：在所有字段后单独定义primary key

create table department3(

id int,

name varchar(20),

comment varchar(100),

primary key(id)

# constraint pk_name primary key(id) #创建主键并为其命名pk_name

);

2多列做主键(复合主键)

create table service(

ip varchar(15),

port char(5),

service_name varchar(10) not null,

primary key(ip,port)

);

FOREIGN KEY

外键的使用条件：

1.两个表类型必须是InnoDB表，MyISAM表暂时不支持外键(据说以后的版本有可能支持，但至少目前不支持)；

2.外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立；

3.外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

基本操作

1创建外表：

第一种方法：建立从表的时候就和主表建立外键

CREATE TABLE TABLE_NAME(

'字段' 类型,

'字段' 类型,

... ...

FOREIGN KEY (从表字段) REFERENCES 主表(字段)

);

第二种方法：建表完成之后,也可以通过sql语句和主表建立联系

ALTER TABLE 从表 ADD CONSTRAINT

外键名称(形如：FK_从表_主表)

FOREIGN KEY (从表字段)

REFERENCES 主表(字段);

2删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称

表与表的对应关系

通过外键，我们可以快速建立表与表之间的关系

多对一：左表多条记录对应右表一条记录

一对多：左表一条记录对应右表多条记录

一对一：左表一条记录对应右表一条记录

多对多：左表多条记录对应右表多条记录

例：出版社、作者信息、书

出版社出版多本书(一对多)

一个作者写一本书(一对一)

多个出版社出版同一作者的多本书(多对多)

多对一：

# 首先应该创建被关联的表

主表

create table press(

id int primary key auto_increment,

name varchar(20)

);

从表

create table book(

id int primary key auto_increment,

name varchar(20),

press_id int not null,

foreign key(press_id) references press(id)

on delete cascade

on update cascade

);

....

多对多：

create table author(

id int primary key auto_increment,

name varchar(20)

);

# 创建一张新表来存放作者表与书表的关系

create table author2book(

id int not null unique auto_increment,

author_id int not null,

book_id int not null,

constraint fk_author foreign key(author_id) references author(id) #创建外键并命名为fk_author

on delete cascade

on update cascade,

constraint fk_book foreign key(book_id) references book(id)

on delete cascade

on update cascade,

primary key(author_id,book_id)

);

一对一：

create table customer(

id int primary key auto_increment,

name varchar(20) not null,

qq varchar(10) not null,

phone char(16) not null

);

create table student(

id int primary key auto_increment,

class_name varchar(20) not null,

customer_id int unique, 　　　　　　　　　　　　 #该字段一定要是唯一的

foreign key(customer_id) references customer(id) #外键的字段一定要保证unique

on delete cascade

on update cascade

);

UNIQUE KEY

设置唯一的约束

方法一：在某一个字段后用 unique

create table department1(

id int,

name varchar(20) unique,

comment varchar(100)

);

方法二：在所有的字段后单独定义unique

create table department2(

id int,

name varchar(20),

comment varchar(100),

constraint uk_name unique(name)

);

例如：

mysql> insert into department1 values(1,'IT','技术');

Query OK, 1 row affected (0.00 sec)

mysql> insert into department1 values(1,'IT','技术');

ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'

联合唯一：

create table service(

id int primary key auto_increment,

name varchar(20),

host varchar(15) not null,

port int not null,

unique(host,port) #联合唯一

);

mysql> insert into service values

-> (1,'nginx','192.168.0.10',80),

-> (2,'haproxy','192.168.0.20',80),

-> (3,'mysql','192.168.0.30',3306)

-> ;

Query OK, 3 rows affected (0.01 sec)

Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80);

ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'

NOT NULL

是否可空，null表示空，非字符串

not null - 不可空

null - 可空

mysql> create table t2(id int not null); #设置字段id不为空

mysql> desc t2;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id | int(11) | NO | | NULL | |

+-------+---------+------+-----+---------+-------+

AUTO_INCREMENT

约束字段为自动增长，被约束的字段必须同时被key约束

1创建自增字段

create table student(

id int primary key auto_increment,

name varchar(20),

sex enum('male','female') default 'male'

);

# 当不指定id时，会按照自增的id增长下去，当指定id时，会按照指定的id进行增长

2删除自增字段

truncate student; # 请空表

注意：对于自增的字段，使用delete删除后，在插入值，该字段仍按照删除前的位置继续增长

3设置自增字段

设置自增字段的起始值 auto_increment

mysql> alter table student auto_increment=3;

mysql> show create table student;

.......

ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

设置步长 auto_increment_increment

#基于会话级别

set session auth_increment_increment=2 #修改会话级别的步长

#基于全局级别的

set global auth_increment_increment=2 #修改全局级别的步长(所有会话都生效)

起始值auto_increment_offset

注意：

如果auto_increment_offset的值大于auto_increment_increment的值，则auto_increment_offset的值会被忽略

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;