自增长 外键约束

1. 自增长

作用： 自动递增

-- 自增长

CREATE TABLE student(

       idINT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT, -- 自增长，从0开始  ZEROFILL 零填充

       NAMEVARCHAR(20)

)

-- 自增长字段的主键字段可以不赋值，自动递增

INSERT INTO student(NAME) VALUES('张三');

INSERT INTO student(NAME) VALUES('李四');

INSERT INTO student(NAME) VALUES('王五');

 

 

SELECT * FROM student;

DELETE FROM student; --自增长的约束不删除

TRUNCATE TABLE student; -- 可以删除自增长约束

2.外键约束

作用：约束两张表的数据

--创建员工表

CREATE TABLE employee(

       idINT PRIMARY KEY,

       empNameVARCHAR(20),

       deptNameVARCHAR(20) –部门名称

)

-插入员工表的数据：

INSERT　INTO　employee　VALUES（1，”张三”，“事业部“）;

INSERT　INTO　employee　VALUES（2，”李四”，“人力资源部“）;

INSERT　INTO　employee　VALUES（3，”王五”，“研发部“）;

--查看表

SELECT*FROM employee；

 

每次添加员工是都要输入部门名称，比较麻烦，数据冗余较大，现在将部门名称用另一张表表示来约束本employee员工表。

--创建部门表

CREATE TABLE dept(

       id INT PRIMARY KEY,

       deptNameVARCHAR(20)

)

--插入部门数据

INSERT INTO dept(id,deptName) VALUES(1,'事业部');

INSERT INTO dept(id,deptName) VALUES(2,'HR');

INSERT INTO dept(id,deptName) VALUES(3,'研发部');

--修改员工表

CREATE TABLE employee(

       idINT PRIMARY KEY,

       empNameVARCHAR(20),

       deptIdINT –部门名称改为部门（dept）的ID

)

--插入员工信息

INSERT　INTO　employee　VALUES（１，”张三”，1）;

INSERT　INTO　employee　VALUES（2，”李四”，2）;

INSERT　INTO　employee　VALUES（3，”王五”，3）;

--但是这里直接看不见部门名称

 

--部门中没有4，所以错误

INSERT　INTO　employee　VALUES（4，”王五”，4）;

--限制插入不合法的数据现在就需要外键约束（这里约束deptID）

--重新建立员工表表

CREATE TABLE employee(

       idINT PRIMARY KEY,

       empNameVARCHAR(20),

       deptIdINT,-- 把部门名称改为部门ID

       --声明一个外键约束，fk就是外键的缩写

    CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) –声明外键    

                                                                     外键名称              外键         参考表(参考字段)

)

--这时插入4部门会报错

INSERT　INTO　employee　VALUES（4，”王五”，4）;

--注意重新建立表的=时候记得删除以前建立的表，被约束字段的表称为副表，约束的表称为主表。这里employee为副表。dept为主表；添加数据： 先添加主表，再添加副表；修改数据：先修改副表，再修改主表；删除数据： 先删除副表，再删除主表。

 

级联操作

--我们希望直接修改主表的数据来影响副表的数据，这时候就需要级联操作；

--创建级联的主表形式是在外键的基础上加ON UPDATE CASCADE这里的UPDATE可以改为DELETE或者添加DELETE，这时候可以有修改和删除两种操作

CREATE TABLE employee(

       idINT PRIMARY KEY,

       empName  VARCHAR(20),

       deptId  INT,-- 把部门名称改为部门ID

       --声明一个外键约束，fk就是外键的缩写

    CONSTRAINT  emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id)  ON UPDATE CASCADE   –-声明级联修改   

)

UPDATE dept SET id=5 WHRER id=4;--修改主表的数据可以改变附表的数据

--如果我们想级联删除，则需要添加ON DELETE CASCADE

CONSTRAINT emlyee_dept_fk  FOREIGN KEY(deptId) REFERENCES dept(id)  ONUPDATE CASCADE ON DELETE CASCADE

DELETE FROM dept WHERE Id=5；--删除主表部门为5的数据，同时附表中5部门的员工也删除