MySQL(十五)DDL之常见的约束

一、常见的约束

NOT NULL:非空,该字段的值必填

UNIQUE:唯一,该字段的值不可重复

DEFAULT:默认,该字段的值不用手动插入有默认值

PRIMARY KEY:主键,该字段的值不可重复并且非空 unique+not null

FOREIGN KEY:外键,该字段的值引用了另外的表的字段

主键和唯一

1、区别:

①、一个表至多有一个主键,但可以有多个唯一

②、主键不允许为空,唯一可以为空

2、相同点

都具有唯一性

都支持组合键,但不推荐

外键:

1、用于限制两个表的关系,从表的字段值引用了主表的某字段值

2、外键列和主表的被引用列要求类型一致,意义一样,名称无要求

3、主表的被引用列要求是一个key(一般就是主键)

4、插入数据,先插入主表

5、删除数据,先删除从表

可以通过以下两种方式来删除主表的记录

#方式一:级联删除

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

#方式二:级联置空

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

二、创建表时添加约束

create table 表名(

字段名 字段类型 not null,#非空

字段名 字段类型 primary key,#主键

字段名 字段类型 unique,#唯一

字段名 字段类型 default 值,#默认

constraint 约束名 foreign key(字段名) references 主表(被引用列)

);

注意:

列级约束: 除了外键其他类型都支持,不可以起约束名;

表级约束: 除了非空和默认其他类型都支持,可以起约束名,但对主键无效。

列级约束可以在一个字段上追加多个,中间用空格隔开,没有顺序要求

三、修改表时添加或删除约束

1、非空

添加非空

alter table 表名 modify column 字段名 字段类型 not null;

删除非空

alter table 表名 modify column 字段名 字段类型 ;

2、默认

添加默认

alter table 表名 modify column 字段名 字段类型 default 值;

删除默认

alter table 表名 modify column 字段名 字段类型 ;

3、主键

添加主键

alter table 表名 add【 constraint 约束名】 primary key(字段名);

删除主键

alter table 表名 drop primary key;

4、唯一

添加唯一

alter table 表名 add【 constraint 约束名】 unique(字段名);

删除唯一

alter table 表名 drop index 索引名;

5、外键

添加外键

alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表(被引用列);

删除外键

alter table 表名 drop foreign key 约束名;

四、自增长列

特点:

1、不用手动插入值,可以自动提供序列值,默认从1开始,步长为1

auto_increment_increment

如果要更改起始值:手动插入值

如果要更改步长:更改系统变量

set auto_increment_increment=值;

2、一个表至多有一个自增长列

3、自增长列只能支持数值型

4、自增长列必须为一个key

(1)创建表时设置自增长列

create table 表(

字段名 字段类型 约束 auto_increment

)

(2)修改表时设置自增长列

alter table 表 modify column 字段名 字段类型 约束 auto_increment

(3)删除自增长列

alter table 表 modify column 字段名 字段类型 约束

五、实践

(1)创建数据库

CREATE DATABASE students;

(2)USE students;

(3)创建科目表,添加主键约束

CREATE TABLE major(id INT PRIMARY KEY,majorName VARCHAR(20));

(4)创建学生信息表,添加列级约束

CREATE TABLE stuinfo(

id INT PRIMARY KEY,#主键

stuName VARCHAR(20) NOT NULL UNIQUE,#非空

seat INT UNIQUE,#唯一

age INT DEFAULT 18,#默认约束

majorId INT REFERENCES major(id)#外键

);

(5)查看stuinfo中的所有索引,包括主键、外键、唯一

SHOW INDEX FROM stuinfo;

(6)添加表级约束

通用的写法

(7)修改表时添加约束,先重新创建一个表

(8)添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

(9)添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

(10)添加主键

①列级约束

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

②表级约束

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

(11)添加唯一

①列级约束

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

②表级约束

ALTER TABLE stuinfo ADD UNIQUE(seat);

(12)添加外键

ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

(13)删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

(14)删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT ;

(15)删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY;

(16)删除唯一

ALTER TABLE stuinfo DROP INDEX seat;

(17)删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

原文发布于微信公众号 - Java后端生活(javaNotebook)

原文发表时间:2018-05-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏土豆专栏

Java面试之数据库常用语句

>id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, //id值,无符号、非空、递增——唯一性,可做主键。

20270
来自专栏测试开发架构之路

MySQL/Oracle索引的创建与使用

MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。

9220
来自专栏PHP在线

MySQL DELETE语句和TRUNCATE TABLE语句的区别

MySQL DELETE语句和TRUNCATE TABLE语句功能相似,但是二者究竟有何区别呢?下文就将为您分析MySQL DELETE语句和TRUNCATE ...

401140
来自专栏极客生活

mysql 数据去重

从excel中导入了一部分数据到mysql中,有很多数据是重复的,而且没有主键,需要按照其中已经存在某一列对数据进行去重。

14520
来自专栏PHP在线

MySQL索引类型一览 让MySQL高效运行起来

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。 在数据库表中,对字段建立索引可以大大提高...

36050
来自专栏杨建荣的学习笔记

MySQL修改数据类型的问题总结(r10笔记第74天)

昨天快下班的时候,突然开发的同事找我说有个紧急需求,负责这个业务的DBA同事回家了,想让我帮忙看看,运行个SQL语句,几秒钟就好。我一听,就本着人道主义的精神留...

356100
来自专栏PHP在线

MyISAM InnoDB 区别(回顾)

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。 在数据库表中,对字段建立索引可以大大提高查...

35150
来自专栏散尽浮华

mysql操作命令梳理(2)-alter(update、insert)

在mysql运维操作中会经常使用到alter这个修改表的命令,alter tables允许修改一个现有表的结构,比如增加或删除列、创造或消去索引、改变现有列的类...

22560
来自专栏battcn

MySQL - 索引详解

索引依托于存储引擎的实现,因此,每种存储引擎的索引都不一定完全相同,并且每种存储引擎也不一定支持所有索引类型。所有存储引擎支持每个表至少16个索引,总索引长度至...

15220
来自专栏静默虚空的博客

sql 语言

sql 语言 DDL DDL 全称 Data Definition Language,即数据定义语言。 DATABASE 创建数据库 CREATE DATABA...

21670

扫码关注云+社区

领取腾讯云代金券