MySql 约束条件

目录

• 数据类型的属性
• 约束条件的简单运用
  • 创建表
  • 清空表，并清空主键自增记录
  • 注意
  • 例 1
    • 单列唯一
    • 联合唯一
  • 例 2
  • 例 3
  • 例 4
    • 联合主键
• 联合主键

sql 约束：约束用于限制加入表的数据的类型。是一种限制，它通过对表的行或列的数据做出限制，来确保表数据的完整性、唯一性可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后也可以（通过 ALTER TABLE 语句）。

数据类型的属性

MySQL关键字	含义
NULL	数据列可包含NULL值
NOT NULL	数据列不允许包含NULL值
DEFAULT	默认值
PRIMARY KEY	主键
AUTO_INCREMENT	自动递增，适用于整数类型
UNSIGNED	无符号
CHARACTER SET name	指定一个字符集

1. not null 约束：强制列不接受 NULL 值，强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
   例：create table table_name(id int not null,name varchar(255) not null); # 强制id和name不能为空
2. unique 约束：唯一标识数据库表中的每条记录,确保表中的一列数据没有相同的
   UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
   PRIMARY KEY 拥有自动定义的 UNIQUE 约束。
   每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束
   例：create table 时在 id 列创建 unique 约束

   create table table_name(id int not null,name varchar(255) not null, unique (id));
   

   例2：为多个列定义 unique

   create table table_name(id int not null,name varchar(255) not null, constraint uc_personID unique (id,name))
   

   例3：表已创建情况下，创建 unique 约束

   alter table table_name add unique(id);
   -- 多个
   alter table table_name add constraint uc_personid unique(id,name);
   

   例4：撤销 unique

   alter table table_name drop index uc_personid;
   

3. PRIMARY KEY 约束：PRIMARY KEY 约束唯一标识数据库表中的每条记录。
   主键必须包含唯一的值。
   主键列不能包含 NULL 值。
   每个表都应该有一个主键，并且每个表只能有一个主键。 primary key = unique + not null
   例：create table时在id列创建PRIMARY KEY约束

   create table table_name(id int not null,name varchar(255) not null, PRIMARY KEY(id));
   

   例2：为多个列定义 PRIMARY KEY

   create table table_name(id int not null,name varchar(255) not null, constraint pk_personID PRIMARY KEY (id,name))
   

   例3：表已创建情况下，创建 PRIMARY KEY 约束

   alter table table_name add PRIMARY KEY(id);
   -- 多个
   alter table table_name add constraint pk_personid PRIMARY KEY(id,name);
   

   例4：撤销 PRIMARY KEY

   alter table table_name drop index uc_personid;
   

4. FOREIGN KEY 约束：一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY
   外键
   外键用来在两个表数据之间建立链接，它可以是一列或多列。一个表可以有一个或多个外键
   外键对应得是参照完整性，一个表得外键可以为空值，若不为空值，则每一个外键值必须等于另一个表中主键得某个值。
   FOREIGN KEY 约束用于预防破坏表之间连接的动作。
   FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
   例：create table 时在 id 列创建 FOREIGN KEY 约束

   create table table_name1(id int not null,name varchar(255) not null,Id_P int, PRIMARY KEY(id),FOREIGN KEY (Id_P) REFERENCES table_name2(Id_P));
   

   例 2：为多个列定义 FOREIGN KEY

   create table table_name1(id int not null,name varchar(255) not null,Id_P int, PRIMARY KEY(id) constraint fk_pertb2 FOREIGN KEY (Id_P)
   REFERENCES table_name2(Id_P) )
   

   例 3：表已创建情况下，创建 FOREIGN KEY 约束

   alter table table_name add ADD FOREIGN KEY (Id_P) REFERENCES table_name1(Id_P);
   -- 多个
   alter table table_name add constraint pk_personid PRIMARY KEY(id,name);
   

   例 4：撤销 FOREIGN KEY

   alter table table_name drop FOREIGN KEY fk_pertb2;
   

   外键约束

mysql> create table bookcategory(
    -> category_id int primary key,
    -> category varchar(20),
    -> parent_id int);

mysql> create table bookinfo(
    -> book_id int primary key,
    -> book_category_id int,
    -> constraint fk_cid foreign key(book_category_id) references bookcategory(category_id));

5. CHECK 约束：用于限制列中的值的范围
   如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
   如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。
   例：create table 时在 id 列创建 CHECK 约束

   create table table_name(id int not null,name varchar(255) not null,CHECK(id>0));
   

   例 2：为多个列定义 CHECK 约束

   create table table_name(id int not null,name varchar(255) not null, constraint chk_tbname CHECK(id>0 and name='xxx'));
   

   例 3：表已创建情况下，创建 CHECK 约束

   alter table table_name ADD constraint chk_tbname CHECK (Id_P>0 AND name='xxx'); 
   

   例 4：撤销 CHECK

   alter table table_name drop constraint chk_tbname;
   

6. DEFAULT 约束：用于向列中插入默认值
   如果没有规定其他的值，那么会将默认值添加到所有的新记录。
   例：create table 时在 id 列创建 DEFAULT 约束，类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值

   create table table_name(id int not null,name varchar(255) not null DEFAULT 'lxq',timedate date DEFAULT GETDATE());
   

   例2：表已创建情况下，创建 DEFAULT 约束

   alter table table_name alter name set DEFAULT 'lxq2';
   

   例3：撤销 DEFAULT

   alter table table_name ALTER name DROP DEFAULT;
   

约束条件的简单运用

• primary key：主键，唯一标识，表都会拥有，不设置为默认找第一个 不空，唯一 字段，未标识则创建隐藏字段
• foreign key：外键
• unique：唯一性数据, 该条字段的值需要保证唯一,不能重复
• auto_increment：自增，只能加给key的int类型字段辅助修饰
• not null：不为空 - 针对一些字段，如注册时的用户名，出生人的性别等，这些需求下的字段，只不能设置为 Null，必须要对其赋值
• null 可为空
• default：默认值 - 对有默认值意外的字段进行赋值时，有默认值的字段会被赋默认值
• unsigned：无符号 - 存储的数字从 0 开始
• zerofill：0 填充 - 存整数时数据长度小于取值范围长度，会在数字左方用 0 填充

创建表

mysql>: create table td1 (x int, y int default 0, z int default 100);

清空表，并清空主键自增记录

truncate table 数据库.表名;

注意

1. 键是用来讲的 io 提供存取效率
2. 联合唯一

create table web (
	ip char(16),
	port int,
	unique(ip,port)
);

3. 联合主键

create table web (
	ip char(16),
	port int,
	primary key(ip,port)
);

例 1

单列唯一

create table t20 (
	id int unique
);

联合唯一

create table web (
    ip char(16),
    port int,
    unique(ip,port)
);

如果联合两个字段,两个字段全相同才相同,否则为不同

insert into web values ('10.10.10.10', 3306), ('10.10.10.10', 3306);

• 注:
  1. 表默认都有主键, 且只能拥有一个主键字段(单列主键 | 联合主键)
  2. 没有设置主键的表, 数据库系统会自上而下将第一个规定为 unique not null 字段自动提升为 primary key 主键
  3. 如果整个表都没有 unique not null 字段且没有 primary key 字段, 系统会默认创建一个隐藏字段作为主键
  4. 通常必须手动指定表的主键, 一般用 id 字段, 且 id 字段一般类型为 int, 因为 int 类型可以 auto_increment

例 2

create table t21(id int auto_increment); -- 自增约束必须添加给 key 的字段

例 3

create table t21(id int primary key auto_increment); -- 自增要结合 key,不赋值插入,数据会自动自增, 且自增的结果一直被记录保留

例 4

联合主键

联合主键

create table t22(
	ip char(16),
    port int,
    primary key(ip,port)
);

如果联合两个字段,两个字段全相同才相同,否则为不同

insert into web values ('10.10.10.10', 3306), ('10.10.10.10', 3306);