关于mysql:mysql约束

45次阅读

共计 5399 个字符，预计需要花费 14 分钟才能阅读完成。

数据库表的束缚

概述

对表中的数据进行限度，保证数据的正确性、有效性和完整性。一个表如果增加了束缚，不正确的数据将无奈插入到表中。束缚在创立表的时候增加比拟适合。

分类

束缚名 束缚关键字
主键 primary key
惟一 unique
非空 not null
外键 foreign key
查看束缚 check 注：mysql 不反对

主键束缚

作用

用来惟一标识数据库中的每一条记录。

哪个字段应该作为表的主键？

通常不必业务字段作为主键，独自给每张表设计一个 id 的字段，把 id 作为主键。主键是给数据库和程序应用 的，不是给最终的客户应用的。所以主键有没有含意没有关系，只有不反复，非空就行。如：身份证，学号不倡议做成主键

主键的特点

  1. 惟一且非空
  2. 一张表只能有一个主键

格局

  1. 在创立表的时候给字段增加主键

    字段名 字段类型 PRIMARY KEY

  2. 在已有表中增加主键

    ALTER TABLE 表名 ADD PRIMARY KEY(字段名);

  3. 删除主键

    ALTER TABLE 表名 DROP PRIMARY KEY;

代码演示

-- 创立表学生表 student01, 蕴含字段 (id, name, age) 将 id 做为主键
create table student01 (
    id int primary key, -- id 为主键
    name varchar(20),
    age int
);

-- 删除 student01 下面的主键
alter table student01 drop primary key;

-- 从新为表 student01 增加主键
alter table student01 add primary key(id);

主键自增

  • 主键如果让咱们本人增加很有可能反复, 咱们通常心愿在每次插入新记录时, 数据库主动生成主键字段的值

    AUTO_INCREMENT：示意主动增长，但字段类型必须是整数类型

  • 默认 AUTO_INCREMENT 的开始值是 1，如果心愿批改起始值, 请应用下列 SQL 语法

    AUTO_INCREMENT= 初始值

  • 创立好当前批改起始值（只能往大的批改）

    ALTER TABLE 表名 AUTO_INCREMENT = 初始值;

  • 创立表后，删除自增

    ALTER TABLE 表名 MODIFY 字段名 字段类型;

  • 创立表后，增加自增

    ALTER TABLE 表名 MODIFY 字段名 字段类型 AUTO_INCREMENT;

  • DELETE 和 TRUNCATE 对自增长的影响

    DELETE：删除所有的记录之后，自增长没有影响。

    TRUNCATE：删除当前，自增长又从新开始。

代码演示

-- 创立表 student02, 蕴含字段 (id, name, age) 将 id 做为自增主键，起始值为 5
create table student02 (
    id int primary key auto_increment, -- 自增主键
    name varchar(20),
    age int
) auto_increment = 5;

-- 直接插入数据，主键会主动增长
insert into student02 (name, age) values('Jason', 21);
insert into student02 (name, age) values('June', 22);
insert into student02 (name, age) values('Charlie', 23);

-- 创立好当前批改起始值为 10
alter table student02 auto_increment = 10;

-- 插入数据
insert into student02 values (null, 'Bob', 24);
insert into student02 values (null, 'Sql', 25);
insert into student02 values (null, 'My', 26);

-- DELETE：删除所有的记录之后，自增长没有影响。delete from student02;

-- 插入数据
insert into student02 values (null, 'is', 30);
insert into student02 values (null, 'it', 31);
insert into student02 values (null, 'just me', 32);

-- TRUNCATE：删除当前，自增长又从新开始。truncate table student02;

-- 插入数据
insert into student02 values (null, 'Sasha', 36);
insert into student02 values (null, 'Sloan', 40);

-- 删除自增
alter table student02 modify id int(11);

-- 再次增加自增
alter table student02 modify id int(11) auto_increment;

惟一束缚

概述

束缚表中某一列不能呈现反复的值（能够有多个NULL）

格局

  • 创立表时，设置惟一束缚

    字段名 字段类型 UNIQUE;

  • 在创立表后，为字段增加惟一束缚

    ALTER TABLE 表名 ADD [CONSTRAINT 惟一束缚名] UNIQUE (列名);

  • 删除惟一束缚

    ALTER TABLE 表名 DROP INDEX 列名 / 惟一束缚名;

代码演示

-- 创立学生表 student03, 蕴含字段(id, name),name 这一列设置惟一束缚, 不能呈现同名的学生
create table student03 (
    id int,
    name varchar(20) unique -- 设置惟一束缚
);


-- 增加一个同名的学生
insert into student03 (id, name) values (1, 'Jason');
insert into student03 (id, name) values (2, 'Jason');

-- 能够存在多个 null
insert into student03 (id, name) values (3, null);
insert into student03 (id, name) values (4, null);

--  创立表后，为列名 id 设置惟一束缚
alter table student03 add constraint unique_id UNIQUE (id);

-- 删除列名 id 的惟一束缚
alter table student03 drop index unique_id;

非空束缚

概述

规定某一列不能为 null。

格局

  • 创立非空束缚

    字段名 字段类型 NOT NULL

  • 删除非空束缚

    ALTER TABLE 表名 MODIFY 字段名 字段类型 NULL;
或
ALTER TABLE 表名 CHANGE COLUMN 字段名 字段名 字段类型 NULL;

  • 创立表后，为字段设置非空束缚

    ALTER TABLE 表名 MODIFY 字段名 字段类型 NOT NULL;
或
ALTER TABLE 表名 CHANGE COLUMN 字段名 字段名 字段类型 NOT NULL;

代码演示

-- 创立表学生表 student04, 蕴含字段 (id,name,gender) 其中 name 不能为 NULL
create table student04 (
    id int,
    name varchar(20) not null, -- 非空束缚
    gender varchar(4)
);

-- 增加一条记录其中姓名不赋值
insert into student04 values (1, null, '男');


-- 删除字段名为 name 的非空束缚
alter table student04 change column name name varchar(20) NULL;

-- 从新为字段名为 name 增加上非空束缚
alter table student04 change column name name varchar(20) NOT NULL;

默认值

概述

给字段默认赋一个值。

格局

字段名 字段类型 DEFAULT 默认值;

代码演示

-- 创立一个学生表 student05，蕴含字段(id,name,address)，地址默认值是广州
create table student05 (
    id int,
    name varchar(20),
    address varchar(100) default '广州'
);

-- 增加一条记录, 应用默认地址
insert into student05 (id,name) values(1, 'Jason');

疑难: 如果一个字段设置了非空与惟一束缚，该字段与主键的区别?

  1. 主键数在一个表中，只能有一个。不能呈现多个主键。主键能够单列，也能够是多列。
  2. 自增长只能用在主键上

外键束缚

概述

  • 外键：在从表中与主表主键对应的那一列
  • 主表：一方，用来束缚他人的表
  • 从表：多方，被他人束缚的表

特点

  1. 必须先删除了从表，能力删除主表
  2. 从表的外键值能够为NULL，然而不能为主表主键值外不存在的值

格局

  • 新建表时减少外键

    [CONSTRAINT] [外键束缚名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名);

  • 已有表减少外键

    ALTER TABLE 从表 ADD [CONSTRAINT] [外键束缚名称] FOERIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

  • 删除外键

    ALTER TABLE 从表 DROP FOREIGN KEY 外键名称;

代码演示

-- 创立表 department，作为主表
create table department (
    id int primary key auto_increment,
    dep_name varchar(20),
    dep_location varchar(20)
);

-- 向主表增加数据
insert into department values (null, '研发部', '北京');
insert into department values (null, '开发部', '上海');

-- 创立从表 employee 并增加外键束缚 emp_depid_fk
create table employee (
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int, -- 外键对应主表的主键
    -- 创立外键束缚
    constraint emp_depid_fk foreign key (dep_id) references department (id)
);

-- 向从表增加数据
insert into employee (name, age, dep_id) values ('Jason', 21, 2);
insert into employee (name, age, dep_id) values ('Charlie', 22, 1);
insert into employee (name, age ,dep_id) values ('June', 23, 1);
insert into employee (name, age, dep_id) values ('Bob', 25, 2);
insert into employee (name, age ,dep_id) values ('Run', 19, 2);
insert into employee (name, age, dep_id) values ('Cat', 26, 1);

-- 尝试往从表中插入不存在的部门（报错）insert into employee (name, age, dep_id) values ('KOK', 48, 3);

-- 删除 employee 表的 emp_depid_fk 外键
alter table employee drop foreign key emp_depid_fk;

外键的级联

在批改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作。

级联操作语法 形容
ON UPDATE CASCADE 级联更新，只能是创立表的时候创立级联关系。
更新主表中的主键，从表中的外键 列也主动同步更新
ON DELETE CASCADE 级联删除

代码演示

-- 删除 employee 表
drop table employee;

-- 从新创立 employee 表，增加级联更新和级联删除
create table employee (
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int, -- 外键对应主表的主键
    constraint emp_depid_fk foreign key (dep_id) references department (id) 
    on update cascade on delete cascade -- 创立外键束缚, 增加级联更新、级联删除
);

-- 再次增加数据到 employee 表和 department 表
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);

-- 把部门表中 id 等于 1 的部门改成 id 等于 10
update department set id = 10 where id = 1;


-- 不能间接删除主表 department, 必须先删除了从表
drop table department;


-- 删除部门号是 2 的部门
delete from department where id = 2;

正文完
mysql
发表至： mysql
2022-09-24
 0
关于mysql:从零开始的常用MySQL语句练习大全
关于mysql:my2sql工具之快速入门
关于mysql:KunlunBase读写分离方案
关于mysql:北亚数据恢复输入错误命令导致MySQL数据库全表数据被删除的数据恢复案例分享