3.4完整性约束

最新推荐文章于 2023-03-23 00:15:00 发布

Mm901

最新推荐文章于 2023-03-23 00:15:00 发布

阅读量111

点赞数

分类专栏： mysql 数据库

本文链接：https://blog.csdn.net/zhjulia123/article/details/81632898

版权

mysql 同时被 2 个专栏收录

8 篇文章 0 订阅

订阅专栏

数据库

5 篇文章 0 订阅

订阅专栏

--一、主键约束，两种方式
--第一种
mysql> drop table employee_tbl;
Query OK, 0 rows affected

mysql> show tables;
+--------------------+
| Tables_in_learnsql |
+--------------------+
| products_tbl       |
| test_increment     |
+--------------------+
2 rows in set

mysql> CREATE TABLE EMPLOYEE_TBL
(EMP_ID		CHAR(9)		NOT NULL PRIMARY KEY,

EMP_NAME	VARCHAR(40)	NOT NULL,
EMP_ST_ADDR VARCHAR(20) NOT NULL,
EMP_CITY	VARCHAR(15) NOT NULL,
EMP_ST		CHAR(2)		NOT NULL,
EMP_ZIP		INTEGER(5)	NOT NULL,
EMP_PHONE	INTEGER(10)	NULL,
EMP_PAGER	INTEGER(10)	NULL);
Query OK, 0 rows affected

mysql> SHOW TABLES;
+--------------------+
| Tables_in_learnsql |
+--------------------+
| employee_tbl       |
| products_tbl       |
| test_increment     |
+--------------------+
3 rows in set

--第二种
mysql> DROP TABLE EMPLOYEE_TBL;
Query OK, 0 rows affected

mysql> CREATE TABLE EMPLOYEE_TBL
(EMP_ID		CHAR(9)		NOT NULL,
EMP_NAME	VARCHAR(40)	NOT NULL,
EMP_ST_ADDR VARCHAR(20) NOT NULL,
EMP_CITY	VARCHAR(15) NOT NULL,
EMP_ST		CHAR(2)		NOT NULL,
EMP_ZIP		INTEGER(5)	NOT NULL,
EMP_PHONE	INTEGER(10)	NULL,
EMP_PAGER	INTEGER(10)	NULL,
PRIMARY KEY(EMP_ID));
Query OK, 0 rows affected

mysql> SHOW TABLES;
+--------------------+
| Tables_in_learnsql |
+--------------------+
| employee_tbl       |
| products_tbl       |
| test_increment     |
+--------------------+
3 rows in set

--二、唯一性约束
mysql> CREATE TABLE EMPLOYEE_TBL
(EMP_ID		CHAR(9)		NOT NULL PRIMARY KEY,
EMP_NAME	VARCHAR(40)	NOT NULL,
EMP_ST_ADDR VARCHAR(20) NOT NULL,
EMP_CITY	VARCHAR(15) NOT NULL,
EMP_ST		CHAR(2)		NOT NULL,
EMP_ZIP		INTEGER(5)	NOT NULL,
EMP_PHONE	INTEGER(10)	NULL		UNIQUE,
EMP_PAGER	INTEGER(10)	NULL);
Query OK, 0 rows affected

mysql> show tables;
+--------------------+
| Tables_in_learnsql |
+--------------------+
| employee_tbl       |
| products_tbl       |
| test_increment     |
+--------------------+
3 rows in set

--三、外键约束:创建一个名为EMP_ID_FK的外键约束，将EMP_ID与EMPLOYEE_TBL中的EMP_ID关联
mysql> CREATE TABLE EMPLOYEE_PAY_TST
(EMP_ID				CHAR(9)			NOT NULL,
 POSITION			VARCHAR(15)		NOT NULL,
 DATE_HIRE			DATE			NULL,
 PAY_RATE			DECIMAL(4,2)	NOT NULL,
 DATE_LAST_RAISE	DATE			NULL,
 CONSTRAINT EMP_ID_FK	FOREIGN	KEY(EMP_ID) REFERENCES	EMPLOYEE_TBL
 (EMP_ID));
Query OK, 0 rows affected


--利用ALTER TABLE添加外键
mysql> CREATE TABLE EMPLOYEE_PAY_TBL
(EMP_ID				CHAR(9)			NOT NULL,
 POSITION			VARCHAR(15)		NOT NULL,
 DATE_HIRE			DATE			NULL,
 PAY_RATE			DECIMAL(4,2)	NOT NULL,
 DATE_LAST_RAISE	DATE			NULL);
Query OK, 0 rows affected

mysql> ALTER TABLE EMPLOYEE_PAY_TBL
ADD CONSTRAINT ID_FK FOREIGN KEY (EMP_ID)
REFERENCES EMPLOYEE_TBL(EMP_ID);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0