MYSQL 约束添加
2022/4/9 19:19:35
本文主要是介绍MYSQL 约束添加,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
一、对已存在的表添加主键约束
mysql> alter table dept add primary key(deptno); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from dept; +--------+--------------+ | deptno | dept_name | +--------+--------------+ | 1 | zhaoyang | | 2 | zhouwenqiang | | 3 | 实施部 | | 4 | 产品部 | +--------+--------------+ 4 rows in set (0.00 sec) mysql>
2. 第二种格式:alter table 表名 add constraint 主键约束的名字 primary key(列名);
mysql> update employees set empno = 1 where ename= -> 'zhaoyang'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from employees; +-------+--------------+------+------------+------------+---------+--------+ | empno | ename | age | birth | hiredate | sal | deptno | +-------+--------------+------+------------+------------+---------+--------+ | 1 | zhaoyang | 24 | 1993-07-17 | 2021-10-18 | 5000.00 | 1 | | NULL | chenqian | 21 | 1996-05-12 | 2021-10-21 | 3001.00 | 1 | | NULL | dengdesheng | 19 | 1999-02-25 | 2021-11-15 | 4000.00 | 1 | | NULL | zhouwenqiang | 21 | 2001-01-01 | 2021-08-19 | 7000.00 | 2 | +-------+--------------+------+------------+------------+---------+--------+ 4 rows in set (0.00 sec) mysql> update employees set empno=2 where ename='chenqian'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> update employees set empno=3 where ename='dengdesheng'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> alter table employees add constraint pk_emp primary key(empno); ERROR 1138 (22004): Invalid use of NULL value mysql> update employees set empno=4 where ename='zhouwenqiang'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> alter table employees add constraint pk_emp primary key(empno); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0.
第三种格式:alter table 表名 modify 列名 数据类型 primary key;
mysql> alter table employees modify empno int primary key; Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0
删除主键约束:
mysql> alter table employees drop primary key; Query OK, 4 rows affected (0.05 sec) Records: 4 Duplicates: 0 Warnings: 0
二、在创建表的时候同时创建主键约束
第一种格式:
create table 表名(
列名1 数据类型 primary key,
列名2 数据类型 );
mysql> create table if not exists location( -> localno int primary key, -> localname varchar(30)); Query OK, 0 rows affected (0.03 sec)
第二种格式:
create table 表名(
列名1 数据类型,
列名2 数据类型,
constraint 主键约束的名字 primary key(列名1) );
mysql> create table if not exists student_info( -> number int, -> name varchar(5), -> sex enum('M','F'), -> id_number char(18), -> department varchar(30), -> major varchar(30), -> enrollment_time date, -> constraint pk_student_info primary key(number)); Query OK, 0 rows affected, 1 warning (0.01 sec)
第三种格式:
create table 表名(
列名1 数据类型,
列名2 数据类型,
primary key(列名1) );
mysql> create table if not exists student_info( -> number int, -> name varchar(5), -> sex enum('M','F'), -> id_number char(18), -> department varchar(30), -> major varchar(30), -> enrollment_time date, -> primary key(number)); Query OK, 0 rows affected (0.03 sec)
这篇关于MYSQL 约束添加的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-02MySQL集群项目实战:新手入门指南
- 2024-11-02初学者指南:部署MySQL集群资料
- 2024-11-01部署MySQL集群教程:新手入门指南
- 2024-11-01如何部署MySQL集群:新手入门教程
- 2024-11-01部署MySQL集群学习:新手入门教程
- 2024-11-01部署MySQL集群入门:新手必读指南
- 2024-10-23BinLog入门:新手必读的MySQL二进制日志指南
- 2024-10-23Binlog入门:MySQL数据库的日志管理指南
- 2024-10-22MySQL数据库入门教程:从安装到基本操作
- 2024-10-22MySQL读写分离入门教程:轻松实现数据库性能提升