oracle-约束-序列

MYSQL外键(Foreign Key)的使用–修改(增加)外键方法,外键名不能有重复的

概要图

图片 1

本文实例讲述了MySQL使用外键实现级联删除与更新的方法。分享给大家供大家参考,具体如下:

 

一 约束

–问题:往表中插入数据的时候,可能出现一些问题,比如:重复插入数据,内容不对(性别)

–如何保证数据库表中数据的完整性和一致性呢?

约束是强加在表上的规则或条件,确保数据库满足业务规则,保证数据的完整性。

约束又分为列约束(Column Constraint)和表约束(Table
Constraint)。列约束作为列定义的一部分只作用于此列本身。表约束作为表定义的一部分,可以作用于多个列

–常见的约束:主键(primary key),外键(foreign
key),唯一(unique),非空(not
null),默认(default),用户自定义(check)

MySQL支持外键的存储引擎只有InnoDB,在创建外键的时候,要求父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引。在创建索引的时候,可以指定在删除、更新父表时,对子表进行的相应操作,包括RESTRICT、NO
ACTION、SET NULL和CASCADE。其中RESTRICT和NO
ACTION相同,是指在子表有关联记录的情况下父表不能更新;CASCADE表示父表在更新或者删除时,更新或者删除子表对应记录;SET
NULL则是表示父表在更新或者删除的时候,子表的对应字段被SET NULL。

1.今天在建wms中的case_detail中的caseNumber不能与case_head相关联。

1.1 主键约束

–什么是主键约束?主键约束就是给表定义一个主键,什么是主键?

–主键主要是用来保证表记录的唯一非空的。

–建表的时候添加主键

create table student(stuno number(4) primary key,stuname varchar2(10),age number(2),addr varchar(50));

insert into student values(1001,'zhang',18,'luoyang');

 

–再次插入相同记录,违法主键约束,不允许插入

SQL> insert into student values(1001,’zhang’,18,’luoyang’);

insert into student values(1001,’zhang’,18,’luoyang’)

ORA-00001: 违反唯一约束条件 (SYS.SYS_C0010797)

 

–如果主键为null也不允许插入,同样是违反了主键约束

SQL> insert into student values(null,’zhang’,18,’luoyang’);

insert into student values(null,’zhang’,18,’luoyang’)

--第一种
create table student2(
stuno number(5) primary key,
stuname varchar2(8),
age number(2),
addr varchar(50)
)
--第二种
create table student2(
stuno number(5) constraints pk_stuno primary key,
stuname varchar2(8),
age number(2),
addr varchar(50)
)
--第三种
create table student (
stuno number(5),
stuname varchar2(5),
age number(5),
addr varchar(50),
constraints pk_stuo primary key(stuno)
);

 

–创建一张课程表:

create table course(cno number(4) primary key,cname varchar2(20),cscore
number(2));

-创建一张成绩表:学号和课程号共同作为一个主键,称为联合主键

–一张表只能有一个主键

create table score(sno number(4),cno number(4),score
number(5,2),constraint pk_score primary key (sno,cno));

drop table course;

create table course(cno number(4) constraint pk_course primary key
,cname varchar2(20),cscore number(2));

作业:

  1. 删除表空间时,如何删除相关联的数据文件

drop tablespace test_data including contents and datafiles;

因为只有InnoDB引擎才允许使用外键,所以,我们的数据表必须使用InnoDB引擎。我所使用的版本是Mysql5.1版本的,过程如下:

 

1.2 非空约束(not null)

非空(NOT NULL)约束:顾名思义,所约束的列不能为NULL值。否则就会报错 。

举例:

create table user1(id number,name varchar2(30) not null);

insert into user1 values(001,'');//会报错

 

创建数据库:

结果建了数次都提示错误,浪费时间很久。

1.3 唯一约束(unique)

唯一(UNIQUE)约束:在表中每一行中所定义的这列或这些列的值都不能相同。必须保证唯一性。否则就会违反约束条件。

用于指定列的值不能重复,但可以为null。

CONSTRAINT constraint_name UNIQUE (col_name1[, col_name2,…])

举例:

create table user3(id number unique,name varchar2(30));

insert into user3 values(1,'111');

insert into user3 values(1,'111');//报错,唯一性

insert into user3 values(null,111);

 例子

--创建唯一约束的三种方法
--(1)
create table student(
sno number(4) primary key,
sname varchar2(20) not null,
age number(3),
addr varchar2(50),
idcard number(18) unique);
--(2)c
create table student(
sno number(4) primary key,
sname varchar2(20) not null,
age number(3),
addr varchar2(50),
idcard number(18) constraint uk_idcard unique);

--(3)
create table student(
sno number(4) primary key,
sname varchar2(20) not null,
age number(3),
addr varchar2(50),
idcard number(18),constraint uk_idcard unique(idcard));

 

primary key与unique的区别:

1.一张表可以有多个unique(唯一)约束;

2.一张表只能有一个主键;

3.设置为主键的列不能有null值;

Create database test;

 

1.4 非空约束(not null)

非空(NOT NULL)约束:顾名思义,所约束的列不能为NULL值。否则就会报错 。

举例:

create table user1(id number,name varchar2(30) not null);

insert into user1 values(001,”);//会报错

创建两个表,其中第一个表的”id”是第二个表(userinfo)的外键:

关键字: mysql createtable errno 121 150 错误 建表错误 外键 

1.5 检查约束(check)

条件(CHECK)约束:表中每行都要满足该约束条件。条件约束既可以在表一级定义也可以在列一级定义。在一列上可以定义任意多个条件约束。

CONSTRAINT constraint_name CHECK (logical_expression)

举例:

create table user4

(id number primary key,

sal number check(sal>=1000 and sal<=2000),

sex char(2) check(sex in('男','女')));

insert into user4 values(1,5000,'男');//sal列的值不满足1000至2000,报错。
CREATE TABLE `user` (
 `id` int(4) NOT NULL,
 `sex` enum('f','m') DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `userinfo` (
 `sn` int(4) NOT NULL AUTO_INCREMENT,
 `userid` int(4) NOT NULL,
 `info` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`sn`),
 KEY `userid` (`userid`),
 CONSTRAINT `userinfo_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

1.6 外键约束(froeign key)

外键(FOREIGN KEY)约束:用来维护从表(Child Table)和主表(Parent
Table)之间的引用完整性。能够维护数据库的数据一致性,数据的完整性。防止错误的垃圾数据入库;

用于定义主表和从表之间的关系,外键约束要定义在从表上,主表则必需具有主键约束或是unique约束,当定义外键约束后,要求外键列数据必需在主表的主键列存在或是为null。

CONSTRAINT constraint_name

FOREIGN KEY (col_name1[, col_name2,…])

REFERENCES ref_table [(ref_col1[,ref_col2,…])]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ] ]

[ NOT FOR REPLICATION ]

houzhenhua@hope-pact.com

举例:

create table class(id number primary key,name varchar2(32));

create table stus(id number primary key,

name varchar2(36) not null,

classid number references class(id)

);

注意

1.外键指向主键列;

2.外键可以指向unique列;

3.建表时先建主表,再建从表;删除表先删从表,再删主表。

4.外键列属性值要与主键或unique列属性值的类型保持一致

5.外键列的值,必需在主键列中存在。但外键列的值允许为null

6.ON DELETE {CASCADE | NO
ACTION}指定在删除表中数据时,对关联表所做的相关操作。在子表中有数据行与父表中的对应数据行相关联的情况下,如果指定了值CASCADE,则在删除父表数据行时会将子表中对应的数据行删除;如果指定的是NO
ACTION,则SQL Server 会产生一个错误,并将父表中的删除操作回滚。NO ACTION
是缺省值。

例子

create table student(
sno number(4) primary key,
sname varchar2(20) not null,
age number(3),
addr varchar2(50),
idcard number(18));

create table course(
cno number(4)primary key,
cname varchar2(20),
cscore number(2));

--创建外键约束的第一种方式
create table score(
stuno number(4) references student(sno),   --创建第一个外键约束
couno number(4) references course(cno),    --创建第二个外键约束
score number(5,2),
constraint pk_score primary key(stuno,couno));  --创建一个主键约束

--删除表及该表关联的约束
drop table score cascade constraints;

--创建外键约束的第二种方式
create table score(
stuno number(4) constraint fk_stuno references student(sno),  --创建第一个外键约束
couno number(4) constraint fk_couno references course(cno),   --创建第二个外键约束
score number(5,2),
constraint pk_score primary key(stuno,couno));

--创建外键约束的第三种方式
create table score(stuno number(4),
couno number(4),
score number(5,2),
constraint fk_stuno foreign key(stuno) references student(sno),
constraint fk_couno foreign key(couno) references course(cno),
constraint pk_score primary key(stuno,couno));

 

 

insert into student
values(1001,’zhangsan’,19,’henanluoyang’,98765432123456789);

insert into student
values(1002,’lisi’,19,’henanluoyang’,98765432123456789);

insert into student
values(1003,’wangwu’,19,’henanluoyang’,98765432123456789);

–查看student表中所有内容

select * from student;

 

–往course表中插入2条记录

insert into course values(9999,’Java基础’,4);

insert into course values(9998,’数据库基础’,2);

–**查看course表中所有记录

select * from course;

 

–思考:insert into score
values(1003,9997,89);能成功吗?不能,因为course表中不存在课程号为9997的课程

–**思考:insert into score
values(1003,9998,89);能成功吗?可以,因为1003在student表中存在,9998在course表中存在

 

–**总结4:定义外键约束之后,删除父表中的记录,需要先将相关子表的记录删除

delete from student where sno=1003; –删除不掉,提示: 违反完整约束条件
(SYS.SYS_C0010808) – 已找到子记录

–**正确的做法:

delete from score where stuno=1003;  –先删子记录

delete from student where sno=1003;  –后删父记录

注意:

2.增加外键的语句是

1.7 默认值(default)

CONSTRAINT constraint_name

 DEFAULT constant_expression [FOR column_name]

举例:

Create table stu(

id number primary key,

address varchar2(50) default ‘宿舍’);

insert into stu(id)  values(1);

 

 

1、存储引擎必须使用InnoDB引擎;

 

二 约束管理

2、外键必须建立索引;

[sql] 

2.1 添加约束

如果在建表时忘记建立必要的约束,则可以在建表后使用alter
table命令为表增加约束;

注意:

1)增加not null约束使用modify(因为字段(列)默认都是可以为空)

2) 添加默认值 alter table emps modify deptno default 10

2)增加其它四种约束使用add。

1.添加not null约束

alter table 表名 modify 字段名 not null;

2.添加unique(唯一)、primary key(主键)、foreign
key(外键)和check(检查)约束

alter table 表名 add constraint 约束名 约束种类(字段);

示例:
--添加主键约束
alter table stuInfo
add constraint PK_stuNo primary key(stuNo)
--添加唯一键约束
alter table stuInfo
add constraint UQ_stuID unique(stuID)
--添加默认约束
alter table emps modify deptno default 10
--添加检查约束
alter table stuInfo
add constraint CK_stuAge check(stuAge between 15 and 40)
--添加外键约束
alter table stuInfo
add constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)

3、外键绑定关系这里使用了“ ON DELETE CASCADE ” “ON UPDATE
CASCADE”,意思是如果外键对应数据被删除或者更新时,将关联数据完全删除或者相应地更新。更多信息请参考MySQL手册中关于InnoDB的文档;

ALTER TABLE `setting_group`  

2.2 删除约束

当不再需要某个约束时,可以删除。删除约束基本语法:

alter table 表名 drop constratint 约束名称;

 

约束名称指的是:一个表的每一个约束都对应一个名称。约束名称用户没有设置时,系统会自动分配一个名称。

 

在删除主键约束的时候,可能有错误,比如:alter table 表名 drop primary
key;这是因为如果在两张表存在主从关系,那么在删除主表的主键约束时,必需带上cascade
选项。

基本语法:

alter table 表名 drop primary key cascade;

 

好,接着我们再来插入数据测试: