自律详解

放假以前尤其跟自己提起了瞬间2016有个有意思的机能叫 Temporal Table
,今日去看了一晃素材整理一下。

放假此前那一个跟本身提起了一晃2016有个有趣的效用叫 Temporal Table
,前几天去看了弹指间材料整理一下。

mysql–外键(froeign key),mysql–froeign

假定一个实体的某部字段指向另一个实体的主键,就叫做外键
被针对的实体,称之为主实体(主表),也叫父实体(父表)。
顶住指向的实业,称之为从实体(从表),也叫子实体(子表)

 997755.com澳门葡京 1

作用:
用以约束处于关系内的实业
充实子表记录时,是不是有与之对应的父表记录

假若主表没有相关的记录,从表无法插入

 997755.com澳门葡京 2

先插入主表数据后再插入从表数据:

 997755.com澳门葡京 3

 

在剔除或许更新主表记录时,从表应该怎么着处理相关的记录

安装级联操作:
在主表数据发生变动时,与之提到的从表数据应该如何处理
    使用紧要字:
     on update
     on delete
    来标识
允许的级联动作:
cascade关联操作,如若主表被更新或删除,从表也会执行相应的操作
set null,表示从表数据不针对主表任何笔录
restrict:拒绝主表的连锁操作

alter table t_student add foreign key (class_id) references t_class
(class_id)
on delete set null; # 在剔除外键时,将从表的外键值设置为null

修改外键:
先删除那些表的外键,然后再充实
alter table tb_name drop froeign key 外键名称
外键名称在确立外键时方可自定义名称,如果不自定义,会遵守mysql自动生成一个名称
show create table tb_name;
 997755.com澳门葡京 4

alter table t_student drop foreign key t_student_ibfk_1;

 997755.com澳门葡京 5

 

除去外键后不会对表中的数量造成其余影响,改变的只是对表的一种约束

alter table t_student add foreign key (class_id) references t_class
(class_id)
on delete set null; # 在剔除外键时,将从表的外键值设置为null

 

 997755.com澳门葡京 6

 997755.com澳门葡京 7

注:on delete 与on update 可以而且出现,
 但在on delete 或on update 后无法同时出现cascade、set
null、restrict,只可以有一个
有关restrict的外键约束此处没有写,与其他三个相同
 restrict:拒绝主表的相干操作,在主表更新恐怕去除数据时,在从表中留存与主表主键相关的数据,则差异意对主表数据举行更新可能去除

在不设置任何级联关系约束时,主表暗中同意会是restrict

 997755.com澳门葡京 8

在restrict约束下,如若想要删除主表数据,除了能够去除没有与子表数据有关的数目外,
可以先修改子表中的外键(修改时,外键必须也要涉及到主表的主键,否则不可能改改成功)
 997755.com澳门葡京 9

 

也可以先删除与想要删除的主表数据的子表数据,再去删除此条主表数据

 997755.com澳门葡京 10

 

一 约束的概念

本条作用看上去像是临时表,不过其实是系统爱戴的一个历史记录表。(在某个程度上边比起大家手动维护的历史表应该有益于了一点的)

以此作用看上去像是临时表,不过事实上是系统保险的一个历史记录表。(在某个程度上面比起大家手动维护的野史表应该有益于了一些的)

mysql添加外键

997755.com澳门葡京,富含外健的表,要先建把外健作为主贱的表,然后才能建你非凡包括外健的标。
create table A( aaa int primary key,…)

create table B(
bbb int primary key,
aaa int,
… …
foreign key (aaa) references A (aaa) on delete … on update …

)
要注意 B中的aaa类型 和 名字要和A中的完全一样,还有
那个空格你最好有,固然有些时候没什么,不过有时就有标题。

 

自律是强加在表上的条条框框或条件。确保数据库满意工作规则。保障数据的完整性。当对表举行DML或DDL操作时,若是此操作会造成表中的多少违反约束原则或规则的话,系统就会拒绝执行这一个操作。约束可以是列一级别的也得以是表级其余。定义约束时不曾付诸约束的名字,ORACE系统将为该约束自动生成一个名字,其格式为SYS_Cn,其中n为自然数(强烈提出各位在开创表或追加约束时,给约束定义名称。)
在Oracle中,数据完整性可以行使约束、触发器、应用程序(进度、函数)三种艺术来贯彻,在那三种格局中,因为约束易于维护,并且存有最好的品质,所以作为保养数据完整性的首选。
列级约束:
列级定义是在概念列的还要定义约束;
column [CONSTRAINT constraint_name] constraint_type
表级约束:
表级定义是指在概念了具有列后,再定义约束,那里须求专注,not
null约束只可以在列级上定义;
column ,…,
[CONSTRAINT constraint_name] constraint_type (column,…)

简单来说直接的说,它的界面看起来是那样的(就如T1一样)
创造了随后,就会在底下有一个T1History的表中表来记录。

大致直接的说,它的界面看起来是如此的(就如T1一样)
制造了将来,就会在底下有一个T1History的表中表来记录。

mysql主外键关系

— 制造测试主表. ID 是主键.
CREATE TABLE test_main (
id INT,
value VARCHAR(10),
PRIMARY KEY(id)
);

— 创造测试子表.
CREATE TABLE test_sub (
id INT,
main_id INT,
value VARCHAR(10),
PRIMARY KEY(id)
);

暗中认可外键约束措施
mysql> ALTER TABLE test_sub
-> ADD CONSTRAINT main_id_cons
-> FOREIGN KEY (main_id)
-> REFERENCES test_main(id);
-> //
Query OK, 2 rows affected (0.17 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> DELETE FROM
-> test_main
-> WHERE
-> id = 1;
-> //
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
constrai
nt fails (`test`.`test_sub`, CONSTRAINT `main_id_cons` FOREIGN
KEY (`main_id`) R
EFERENCES `test_main` (`id`))

MySQL使用上边那一个讲话删除外键约束
ALTER TABLE test_sub DROP FOREIGN KEY main_id_自律详解。cons;
参考资料:hi.baidu.com/…3.html
 

key),mysql–froeign
假如一个实体的某个字段指向另一个实体的主键,就称为外键
被指向的实业,称之为主实体(主表),…

二 约束功用

997755.com澳门葡京 11

997755.com澳门葡京 12

封锁的成效:完毕部分作业规则,幸免无效的废品数据进入数据库,维护数据库的完整性(完整性指正确性与一致性)。从而使数据库的开销和保证都越来越简单。

 然后我们来试下怎么去玩这些成效。首先,确认你的Sql Server
版本是2016。然后大家经过一个这么的语句来创制表

 然后大家来试下怎么去玩那一个职能。首先,确认你的Sql Server
版本是2016。然后大家由此一个这样的语句来创制表

三 约束的分类

Use Test
go

create table T1(ID int identity primary key,
    COl1 nvarchar(50),
    TimeFrom datetime2 generated always as row start,
    TimeTo datetime2 generated always as row end,
    period for system_time(TimeFrom,TimeTo)) with (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.T1History));
Use Test
go

create table T1(ID int identity primary key,
    COl1 nvarchar(50),
    TimeFrom datetime2 generated always as row start,
    TimeTo datetime2 generated always as row end,
    period for system_time(TimeFrom,TimeTo)) with (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.T1History));

1 not null(非空)
比方在列上定义了not
null,那么当插入数据时,必须为列提供,数据不只怕为NULL。约束只可以在列级定义,无法在表级定义。

 

 

2 unique(唯一)
当定义了唯一约束后,该列值是不只怕重复的,然则足以为null。
3 primary key(主键)
用于唯一的标识表行的多少,当定义主键约束后,该列不但无法重新而且不只怕为NULL。一张表最七只好有一个主键,可是足以由五个unique约束。
创办主键或唯一约束后,ORACLE会自动成立一个与约束同名的目录(UNIQUENES为UNIQUE唯一索引)。必要专注的是:每一种表只好有且有一个主键约束。
4 foreign key(外键)
用来定义主表和从表之间的涉及,外键约束要定义在从表上,紧要则必须持有主键约束或是unique约束,当定义外键约束后,需求外键列数据必须在主表的主键列存在只怕为NULL。
用来敬爱从表(Child Table)和主表(Parent Table)之间的引用完整性.
外键约束是个有争议性的约束,它一方面可以保险数据库的数目一致性,数据的完整性。防止错误的废品数据入库;
其余一方面它会扩充表插入、更新等SQL品质的额外开支,不少系统内部通过作业逻辑控制来裁撤外键约束。例如在数据仓库中,就推荐禁用外键约束。
5 check
用以强制行数据必须满足的规格,假定在sal列上定义了check约束,并须求sal列值在1000~2000中间,若是不在1000~2000中间就会提示出错。

那边小编就概括的开创一个只有自增主键和一个列的表。创造System_Versioning
的表。必须有2个讲明为datetime2
的光阴字段才行,因为要求用那2个字段来记录数据的暴发轨迹。

此地自身就简单的创办一个惟有自增主键和一个列的表。创造System_Versioning
的表。必须有2个申明为datetime2
的光阴字段才行,因为急需用那2个字段来记录数据的发出轨迹。

四 约束命令规范

譬如此处笔者是选取一个TimeFrom
的字段表示数据的出力先河时间,而提姆eTo表示那行数据的失效时间(比方说数据被改动,被删去,那么TimeTo就会记录着修改,删除的年华)

例如那里自身是行使一个TimeFrom
的字段表示数据的成效发轫时间,而TimeTo表示这行数据的失灵时间(比方说数据被改动,被删除,那么TimeTo就会记录着修改,删除的时光)

自律名称提出协调定义一套命名规则,否则使用系统生成的自律名,很难能把它和呼应的表、字段联系起来。
    非空约束     NN_表名_列名
    唯一约束     UK_表名_列名
    主键约束     PK_表名
    外键约束     FK_表名_列名
    条件约束     CK_表名_列名
    专断认同约束     DF_表名_列名
假定封锁名称当先32位长度,提出应当缩写表名,而不使用NN_表名_数字。不过现实视情状而定,很多时候
DF_表名_列名
那样命名,往往超过了32字符。所以有时候须要缩写表面只怕采取其它规则。

下边我们进行测试,先做测试样例,然后再作证

上边我们进行测试,先做测试样例,然后再作证

五 成立约束
1 not null(非空)
创建
SQL> create table t1(id number,name varchar2(20) constraint
nn_t1_id not null);

Step 1:新增多少

Step 1:新增多少

Table created.
SQL> col CONSTRAINT_NAME format A50
SQL> col OWNER format A20
SQL> select constraint_name,constraint_type,owner from
user_constraints;

insert into T1 (Col1) 
    values ('1111'),('2222'),('3333')

select * from T1
select * from T1History

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
1           1111                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999
2           2222                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999
3           3333                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999

(3 行受影响)

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
insert into T1 (Col1) 
    values ('1111'),('2222'),('3333')

select * from T1
select * from T1History

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
1           1111                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999
2           2222                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999
3           3333                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999

(3 行受影响)

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------

CONSTRAINT_NAME C OWNER

 

 


新增的时候,数据都是时尚的本子,所以在历史表里面并不存在记录(注美赞臣(Meadjohnson)(Beingmate)点,提姆eFrom
和TimeTo
那2个字段将由系统控制维护,并不须求手工插入,假设突显写入那一个字段,将抛出荒谬。系统珍爱那2个字段,采取的光阴将选取UTC格式的时光,对于大家国内,就是小时-8的操作)

新增的时候,数据都以新型的本子,所以在历史表里面并不存在记录(注爱他美(Meadjohnson)点,TimeFrom
和TimeTo
这2个字段将由系统控制维护,并不须求手工插入,假使展现写入那一个字段,将抛出荒唐。系统保证那2个字段,采取的大运将利用UTC格式的小运,对于我们国内,就是小时-8的操作)

SYS_C009047 V SCOTT
FK_DEPTNO R SCOTT
SYS_C009046 C SCOTT
NN_T1_ID C SCOTT
PK_DEPT P SCOTT
PK_EMP P SCOTT

 

 

6 rows selected.
修改
SQL> drop table t1 purge;

Step 2:修改数据

Step 2:修改数据

Table dropped.

update T1 set Col1 = Col1+'New' where ID = 2

select * from T1
select * from T1History

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
1           1111                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999
2           2222New                                            2016-10-07 07:30:38.0561513 9999-12-31 23:59:59.9999999
3           3333                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
2           2222                                               2016-10-07 07:28:30.3598532 2016-10-07 07:30:38.0561513
update T1 set Col1 = Col1+'New' where ID = 2

select * from T1
select * from T1History

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
1           1111                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999
2           2222New                                            2016-10-07 07:30:38.0561513 9999-12-31 23:59:59.9999999
3           3333                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
2           2222                                               2016-10-07 07:28:30.3598532 2016-10-07 07:30:38.0561513

SQL> create table t1(id number,name varchar2(20));

 

 

Table created.

修改数据的时候。将在历史表里面写入一条历史记录,并将TimeTo设置为目前涂改的UTC时间,在主表将保存数据的新型版本。

修改数据的时候。将在历史表里面写入一条历史记录,并将TimeTo设置为当下修改的UTC时间,在主表将保存数据的最新版本。

SQL> alter table t1 modify id constraint nn_t1_id not null;

 

 

Table altered.

Step 3:删除

Step 3:删除

SQL> select constraint_name,constraint_type,owner from
user_constraints;

delete from T1 where ID = 3
select * from T1
select * from T1History


ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
1           1111                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999
2           2222New                                            2016-10-07 07:30:38.0561513 9999-12-31 23:59:59.9999999

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
2           2222                                               2016-10-07 07:28:30.3598532 2016-10-07 07:30:38.0561513
3           3333                                               2016-10-07 07:28:30.3598532 2016-10-07 07:32:04.3640717
delete from T1 where ID = 3
select * from T1
select * from T1History


ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
1           1111                                               2016-10-07 07:28:30.3598532 9999-12-31 23:59:59.9999999
2           2222New                                            2016-10-07 07:30:38.0561513 9999-12-31 23:59:59.9999999

ID          COl1                                               TimeFrom                    TimeTo
----------- -------------------------------------------------- --------------------------- ---------------------------
2           2222                                               2016-10-07 07:28:30.3598532 2016-10-07 07:30:38.0561513
3           3333                                               2016-10-07 07:28:30.3598532 2016-10-07 07:32:04.3640717

CONSTRAINT_NAME C OWNER

 

 


去除数据的时候和修改的体制几乎,就是主表删除了行记录,不过历史表保留了一份删除的动作。

去除数据的时候和改动的编制差不离,就是主表删除了行记录,可是历史表保留了一份删除的动作。

FK_DEPTNO R SCOTT
SYS_C009046 C SCOTT
NN_T1_ID C SCOTT
PK_DEPT P SCOTT
PK_EMP P SCOTT
SYS_C009047 V SCOTT
2 unique(唯一)
创建
SQL> drop table t1 purge;

 

 

Table dropped.

简易的测试就可以形成这里。下边还有多少个测试注脚

大致的测试就足以形成这里。上面还有多少个测试阐明

SQL> create table t1(id number,qq number,constraint un_t1_qq
unique(qq));

1 即便接纳Merge,那么Merge做的操作将对应以上的增/删/改来维护版本

1 即使运用Merge,那么Merge做的操作将对应以上的增/删/改来维护版本

Table created.
修改
SQL> drop table t1 purge;

2 使用了经版本维护的表之后,不恐怕应用truncate table
的操作,因为操作不支持

2 使用了经版本维护的表之后,不可以采纳truncate table
的操作,因为操作不支持

Table dropped.

3 drop 表的时候,无法一向动用drop table 语句,须要先用 ALTER TABLE
[dbo].[T1] SET ( SYSTEM_VERSIONING = OFF )
来把系统保证的本子去掉,然后再各自drop 掉当前表和历史表

3 drop 表的时候,不可能直接接纳drop table 语句,须要先用 ALTER TABLE
[dbo].[T1] SET ( SYSTEM_VERSIONING = OFF )
来把系统珍惜的本子去掉,然后再各自drop 掉当前表和历史表

SQL> create table t1(id number,qq number);

4 小编是密集的╮(╯_╰)╭ ~请其余大神指引补充

4 小编是凝聚的╮(╯_╰)╭ ~请其余大神指引补充

Table created.
SQL> alter table t1 add constraint un_t1_qq unique(qq);

5 谢谢@wy123 的提示,创造这种类型的表需求有主键才行~居然忘记了

5 谢谢@wy123 的提示,创建那种类型的表要求有主键才行~居然忘记了

Table altered.
3 primary key(主键)
创建
SQL> drop table t1 purge;

 

 

Table dropped.

 

 

SQL> create table t1(id number,qq number,constraint pk_t1_id
primary key(id));

 

 

Table created.
修改
SQL> drop table t1 purge;

 

 

Table dropped.
SQL> create table t1(id number,qq number);

 

 

Table created.

 

 

SQL> alter table t1 add constraint pk_t1_id primary key(id);

 

 

Table altered.
4 foreign key(外键)
创建
SQL> create table t2(id number,cc number,constraint fk_t2_id
foreign key(id) references t1(id));

 

 

Table created.
修改
SQL> drop table t2 purge
2 ;

 

 

Table dropped.

 

 

SQL> create table t2(id number,cc number);

 

 

Table created.

 

 

SQL> alter table t2 add constraint pk_t2_id foreign key(id)
references t1(id);

 

 

Table altered.
当定义了外部键约束之后,要求外部键列的多少必须在主表的主键列(或惟一列)中存在,只怕为NULL,FOREING
KEY约束既可以在列级定义,也足以在表级定义。

 

 

首要字表明:
(1) FOREING
KEY:该选项用于指定在表级定义外部键约束。当在表级定义外部键约束时务必指定该接纳,在列级定义外部键约束不须求指定该选拔
(2)
REFERENCES:该选项用于指定主表名及其主键列。当定义外部键约束时,该选拔必须指定.。
(3) ON DELETE
CASCAED:该选项用于指定级联删除选项。假若在概念外部键约束时指定了该选项,那么当删除主表数据时会级联删除从表的相关数据。
(4) ON DELECT SET
NULL:该选项用于指定转换相关的外部键值为NULL,要是在概念外部键约束时指定了该选项,那么当删除主表数据时会将从表外部键列的多寡设置为NULL。
5 check(检查性约束)
创建
SQL> create table t3(id number,sal number,constraint ck_t3_sal
check(sal between 5000 and 50000));

 

 

Table created.
修改
SQL> drop table t3 purge;

 

 

Table dropped.

 

 

SQL> create table t3(id number,sal number);
六 维护约束

1 扩张约束
(1) 即使伸张UNIQUE、PRIMARY KEY、FOREIGN KEY 和CKECK 必须利用ALTER
TABLE语句的ADD子句;
(2) 如果增添NOT NULL约束,那么必须使用ALTER
TABLE语句的MODIFY子句,如:
ALTER TABLE table_name ADD [CONSTRAINT constraint_name]
constraint_type (column,…)
ALTER TABLE table_name MODIFY column
[CONSTRAINT constraint_name] NOT NULL;

2 修改约束名
在同一个方案中,约束名必须惟一,并且封锁名也不可以与别的对象同名。当用IMPDP工具恐怕IMP工具导入其他对象时,如察觉有同名的目的,将会出错
语法:
ALTER TABLE table_name RENAME CONSTRAINT old_constraint_name
TO new_constraint_name;
SQL> ALTER TABLE T1 rename constraint pk_t1_id to pk_t1_idnew
2 ;

Table altered.

3 禁止约束

不准约束指使约束临时失效。当禁止了自律之后,约束规则将不再生效。在使用SQL*LOADER或INSERT装载数据以前,为了加紧数据装载速度,应该率先禁止约束,然后装载数据。
语法:
ALTER TABLE table_name
DISABLE CONSTRAINT constaint_name
[CASCAED];–CASCAED用于指定级联禁止从表的外部键
SQL> ALTER TABLE T2 DISABLE constraint pk_t2_id;

Table altered.
4 激活约束

语法:
ALTER TABLE table_name ENABLE CONSTRAINT constraint_name;
SQL> ALTER TABLE T2 enABLE constraint pk_t2_id;

Table altered.
5 删除约束

当删除特定表的主键约束时,假诺该表具有相关的从表,那么在剔除主键约束时务必包涵CASCAED选项
语法:
ALTER TABLE table_name DROP
CONSTRAINT constraint_name |PRIMARY KEY
SQL> ALTER TABLE T3 drop constraint ck_t3_sal;

Table altered.
6 彰显音讯

1.USER_CONSTRAINTS
2.USER_CONS_COLUMNS
3.SQL> set line 100
SQL> col CONSTRAINT_NAME format A20
SQL> col table_NAME format A20
SQL> col column_NAME format A20
SQL> select constraint_name,table_name,column_name from
user_cons_columns where table_name=’T1′;

CONSTRAINT_NAME TABLE_NAME COLUMN_NAME


PK_T1_ID T1 ID

Table created.

SQL> alter table t3 add constraint ck_t3_sal check(sal>5000);

Table altered.

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website