MYSQL数据库3

3. PRIMARY KEY 约束

主键约束相当于非空约束和唯一约束，即主键约束的列既不允许出现重复值，也不允许出现null值; 如果对多列组合建立主键约束,则多列里包含的每一列都不能为空,但只要求这些列组合不能重复。主键列的值可用于唯一地标识表中的一条记录。
每一个表中最多允许有一个主键，但这个主键约束可由多个数据列组合而成。主键是表中能唯一确定一行记录的字段或字段组合。
建立主键约束时既可使用列级约束语法，也可使用表级约束语法，如果需要对
多个字段组合主键约束，则只能使用表级约束语法。 使用表级约束语法建立约束时，可以为该约束指定约束名。但不管用户是否为该主键约束指定约束名，MySQL总是将所有主键约束命名为PRIMARY。
提示:

MSOL允许在建立主键约束时为该约束命名、但这个名字没太任何作用，这是为了保持与标准SQL的兼容性。大部分数据库都允许自行指定主键约束的名字，而且一旦指 定了主键约束名，则该约束名就是用户指定的名字。

当创建主键约束时，MySQL在主键约束所在列或列组合上建立对应的唯一索引。

创建主键约束的语法和创建唯一约束的语法非常像，一样允许使用列级约束语注为单独的数据列创建主键，如果需要为多列组合建立主键约束或者需要为主键约束命名，则应该体田事绍约市语注来建立主键约束。与建立唯一约束不同的是，建立主键约束使用 primary key。

建表时创建主键约束，使用列级约束语法：

create table primary_test
(
# 建立了主键约束
test_id int primary key,
test_name varchar(255)
);

建表时创建主键约束，使用表级约束语法:

create table primary_test2
(
test_id int not null,
test_name varchar(255),
test_pass varchar(255),
# 指定主键约束名为test2_pk，对大部分数据库有效，但对MySQL无效
# MySQL数据库中该主键约束名依然是PRIMARY
constraint test2_pk primary key{test_id)
);

建表时创建主键约束，以多列建立组合主键，只能使用表级约束语法:

create table primary_test3
(
test_name varchar(255),
test_ pass varchar(255),
# 建立多列组合的主键约束
primary key(test name, test pass) 
 );

如果需要删除指定表的主键约束，则在alter table语句后使用drop primary key子句即可。SQL语句如下:

# 删除主键约束
alter table primary_test3
drop primary key;

如果需要为指定表增加主键约束，既可通过modify修改列定义来增加主键约束， 这将采用列级约束语法来增加主键约束;也可通过add来增加主键约束，这将采用表级约束语法来增加主键约束。SQL语句如下:

# 使用表级约束语法增加主键约束
alter table primary_test3
add primary key(test_name,test_pass);

如果只是为单独的数据列增加主键约束，则可使用modify修改列定义来实现。SQL语句如下

#使用列级约束语法增加主键约束
alter table primary_test3
modify test_name varchar(255) primary key;

注意:

不要连续执行上面两条SOL语句，因为上面两条SQL语句都是为primary_test3增加主键约束，而同一个表里最多只能有一个主键约束，所以连续执行上面两条SQL语句肯定出现错误。为了避免这个问题，可以在成功执行了第一条增加主键约束的SQL语句之后，先将primary_test3里的主键约束删除后再执行第二条增加主键约束的SQL语句。

很多数据库对主键列都支持一种自增长的特性 ——如果某个数据列的类型是整型，而且该列作为主键列，则可指定该列具有自增长功能。指定自增长功能通常用于设置逻辑主键列——该列的值没有任何物理意义，仅仅用于标识每行记录。MySQL使用auto_increment来设置自增长，SQL语句如下:

create table primary_test4
(
# 建立主键约束，使用自增长
test_id int auto_increment primary key,
test_name varchar(255),
test_pass varchar(255)
);

一旦指定了某列具有自增长特性，则向该表插入记录时可不为该列指定值，该列的值由数据库系统自动生成。

4. FOREIGN KEY 约束

外键约束主要用于保证一个或两个数据表之间的参照完整性,外键是构建于一个表的两个字段或者两个表的两个字段之间的参照关系。外键确保了相关的两个字段的参照关系:子(从)表外键列的值必须在主表被参照列的值范围之内，或者为空(也可以通过非空约束来约束外键列不允许为空)。

当主表的记录被从表记录参照时，主表记录不允许被删除，必须先把从表里参照该记录的所有记录全部删除后，才可以删除主表的该记录。还有一种方式，删除主表记录时级联删除从表中所有参照该记录的从表记录。

从表外键参照的只能是主表主键列或者唯一键列,这样才可保证从表记录可以准确定位到被参照的主表记录。同一个表内可以拥有多个外键。

建立外键约束时，MySQL也会为该列建立索引。

外键约束通常用于定义两个实体之间的一对多、一对一的关联关系。对于一对多的关联关系，通常在多的一端增加外键列，例如老师一学生(假设一个老师对应多个学生，但每个学生只有一个老师，这是典型的一对多的关联关系)。为了建立他们之间的关联关系，可以在学生表中增加一个外键列，该列中保存此条学生记录对应老师的主键。对于一对一的关联关系，则可选择任意一方来增加外键列，增加外键列的表被称为从表，只要为外键列增加唯一约束就可表示一对一的关联关系了。对于多对多的关联关系，则需要额外增加一个连接表来记录它们的关联关系。

建立外键约束同样可以采用列级约束语法和表级约束语法。如果仅对单独的数据列建立外键约束，则使用列级约束语法即可；如果需要对多列组合创建外键约束，或者需要为外键约束指定名字，则必须使用表级约束语法。

采用列级约束语法建立外键约束直接使用references关键字，references指定该列参照哪个主表，以及参照主表的哪一列。SQL语句如下:

# 为了保证从表参照的主表存在，通常应该先建主表
create table teacher_table
# auto_increment:代表数据库的自动编号策略，通常用作数据表的逻辑主键
teacher_id int auto increment,
teacher name varchar(255),
primary key(teacher_id)
);
create table student_table
(
# 为本表建立主键约束
student_id int auto_increment primary key,
student_name varchar(255),
# 指定 java_teachet 参照到 teacher_table的teacher_id列
java_teachet int references teacher_table(teacher_id)
);

值得指出的是，虽然MySQL支持使用列级约束语法来建立外键约束，但这种列级的外键约束不会生效，MySQL提供这种列级约束语法仅仅是为了和标准SQL保持良好的兼容性。因此，如果要使MySQL中的外键约束生效，则应使用表级约束语法。

# 为了保证从表参照的主表存在，通常应该先建主表
create table teacher_table1
(
  # auto_increment;代表数据库的自动编号策略、通常用作数据表的逻辑主键
  teacher_id int autoincrement,
  teacher_name varchar(255),
  primary key （teacher id)
);
create table student_table1
(
  #为本表建立主键约束
  student_id int auto_increment primary key,
  student_name varchar(255),
  # 指定java_teacher 参照到teacher_table1的teacher_id列
  java_teacher int,
  foreign key (java_teacher) references teacher_table1 （teacher id）
);

如果使用表级约束语法，则需要使用foreign key来指定本表的外键列，并使用references来指定参照哪个主表，以及参照到主表的哪个数据列。使用表级约束语法可以为外键约束指宗约束名，如果创建外键约束时没有指定约束名，则MySQL会为该外键约束命名为table_name_ibfk_n,其中table_name是从表的表名，而n是从1开始的整数。

如果需要显式指定外键约束的名字，则可使用constraint来指定名字。SOL语句如下:

# 为了保证从表参照的主表存在，通常应该先建主表
create table teacher_table2
(
  # auto_increment:代表数据库的自动编号策略，通常用作数据表的逻辑主键
  teacher id int auto_increment,
  teacher name varchar(255),
  primary key(teacher_id)
);
create table student_table2
  # 为本表建立主键约束
  student_id int auto_increment primary key,
  student name varchar (255),
  java_teacher int  
  # 使用表级约束语法建立外键约束，指定外键约束的约束名为student teacher fk
  constraint student_teacher_fk foreign key(java_teacher) references                      
    teacher_table2 (teacher_id)
);

如果需要建立多列组合的外键约束，则必须使用表级约束语法，SQL语句如下

# 为了保证从表参照的主表存在，通常应该先建主表
create table teacher_table3
（
  teacher_name varchar(255),
  teacher_pass varchar(255),
  # 以两列建立组合主键
  primary key(teacher_name, teacher_pass)
);
create table student_table3
  # 为本表建立主键约束
  student_id int auto_increment primary key,
  student_name varchar(255),
  java_teacher_name varchar(255),
  java_teacher_pass varchar(255),
  # 使用表级约束语法建立外键约束，指定两列的联合外键
  foreign key (java_teacher_name, java_teacher_pass)
    references teacher_table3(teacher name, teacher_pass)
);

删除外键约束的语法很简单，在alter table后增加“drop foreign key约束名”子句即可。代码如下

# 删除student_table3表上名为studenttable3_ibfk_1的外键约束
alter table student_table3
drop foreign key student_table3_ibfk_1

增加外键约束通常使用add foreign key命令。SQL语句如下:

# 修改student table3数据表，增加外键约束
alter table student_ table3
add foreign key(java_teacher_name, java_teacher_pass)
references teacher_table3(teacher_ name, teacher_pass);

值得指出的是，外键约束不仅可以参照其他表，而且可以参照自身，这种参照自身的情况通常被称为自关联。例如，使用一个表保存某个公司的所有员工记录，员工之间有部门经理和普通员工之分，部门经理和普通员工之间存在一对多的关联关系，但他们都是保存在同一个数据表里的记录，这就是典型的自关联。下面的SQL语句用于建立自关联的外键约束

# 使用表级约束语法建立外约束键，且直接参照自身
create table foreign test
foreign id int auto_increment primary key,
foreign name varchar(255),
# 使用该表的refer_id参照到本表的foreign_id列
refer_id int,
foreign key(refer_id) references foreign_test(foreign_id)
);

如果想定义当删除主表记录时，从表记录也会随之删除，则需要在建立外键约束后添加on delete cascade或添加on delete set null，第一种是删除主表记录时，把参照该主表记录的从表记录全部级联删除;第二种是指定当删除主表记录时，把参照该主表记录的从表记录的外键设为null。SQL语句如下:

# 为了保证从表参照的主表存在，通常应该先建主表
create table teacher_table4
  # auto increment:代表数据库的自动编号策略，通常用作数据表的逻辑主键
  teacher_id int auto_increment,
  teacher_name varchar(255),
  primary key(teacher_id)
);
create table student_table4
(
  # 为本表建立主键约束
  student id int auto_increment primary key,
  student_name varchar(255),
  java_ teacher int,
  # 使用表级约束语法建立外键约束，定义级联删除
  foreign key(java_teacher) references teacher_table4(teacher_id)
    on delete cascade # 也可用on delete set null
);

5. CHECK约束

当前版本的MySQL支持建表时指定CHECK约束,但这个CHECK约束不会有任何作用。建立CHECK约束的语法很简单，只要在建表的列定义后增加check(逻辑表达式》图可。SQL语句如下：

create table check_test  
(
  emp_id int auto_increment,
  emp_name varchar(255),
  emp salary decimal,
  primary key(emp_id),
  # 建立CHECK约束
  check (emp_salary>0)
);

虽然上面的SQL语句建立的check_test表中有CHECK约束,CHECK约束要求emp_salary大于0,但这个要求实际上并不会起作用。

提示:

MySQL作为一个开源、免费的数据库系统，对有些功能的支持确实不太好。如果读者确实希望MySQL创建的数据表有CHECK约束、甚至有更复杂的完整性约束，则可借助于MySQL的触发器机制。本书不会介绍MySQL的触发器内容，读者可参考其他相关书籍。