Oracle day05 建表_约束

 

表的创建

标准的建表语法 ：

CREATE TABLE [schema.] table (column datatype [DEFAULT expr], ... );

使用子查询创建表的语法

CREATE TABLE table [column(,column...)] AS subquery,

1.新表的字段列表必须与子查询中的字段列表匹配

2.字段列表可以省略

create table emp2 as select * from emp;

表结构的修改

• alter table ... add ... : 增加新的列

alter table emp add address varchar(20)

新增加的类不能定义为 "not null" ,基本表在增加一列后，原有元组在新增加的列上的值都定义为空值。

• alter table ...drop ... : 删除原有的列

语法格式:alter table 表名 drop column 列名

alter table emp drop column address

• alter table ...modify ... : 修改字段

alter table emp modify(job varchar(50))

• drop table : 在基本表不需要时,可以使用语句撤销。

在一个基本表撤销后，所有的数据都丢弃。所有相关的索引被删除

drop table emp cascade constraints

• RENAME : 语句改变表名（视图），要求必须是表（视图）的所有者

RENAME old_name TO new_name

约束constraint

若定义了约束，并且数据不符合约束，那么DML操作（INSERT、UPDATE、DELETE)将不能成功执行

约束名 约束描述

• not null 非空
• unique Key 唯一键
• primary key 主键
• foreign key 外键
• check 自定义检测约束

Oracle 使用SYS_Cn格式命名约束，也可以由用户命名

在建表的同时创建

建表后创建

约束从作用上分类，可以分成两大类：

表级约束：可以约束表中的任意一列或多列。可以定义除了not null 以外的任何约束

列级约束：只能约束其所在的某一列。可以定义任何约束。

列级约束 : 从形式上看，在每列定义完后马上定义的约束，在逗号之前就定义好了。

carete table parent(p1 number primary key); create table child (c1 number primary key ,c2 number references parent(p1))；

表级约束：从形式上可以看出列级约束的区别了吧。

create table child (c1 nnumber ,c2 number ,primary key(c2), foreign key(c2) references parent(p1));

有些时候，列级约束无法实现某种约束的定义，比如联合主键的定义，就要用到表级约束：

create table test(id1 number ,id2 number ,primary key(id1,id2));

主键约束(PRIMARY KEY)

主键不能为空，也不允许出现重复，即关系要满足实体完整性规。

• 主键从功能上看相当于非空且唯一
• 一个表中只允许一个主键
• 主键是表中能够唯一确定一个行数据的字段
• 主键字段可以是单字段或者是多字段的组合
• Oracle 为主键创建对应的唯一性索引

主键可用下列两种形式之一定义

1. primary key（列）主键子句 在表的定义中加上
2. primary key主键短语 在主属性的定义之后加上

上述形式Oracle 会自动命名约束，可自己给约束起名

create table t3( id number(4), constraint t3_pk primary key(id） ） 

非空约束（not null）

• 确保字段值不允许为空
• 只能在字段级定义

CREATE TABLE employees( employee_id number(6), name varchar2(25) not null, salary number(8,2), hire_date date constraint emp_hire_date_nn not null )

 

 

唯一性约束（unique）

• 唯一性约束条件确保所在的字段或者字段组合不出现重复值
• 唯一性约束条件的字段允许出现空值
• Oracle 将为唯一性约束条件创建对应的唯一性索引

create table employees( id number(6), name varchar2(25) not null unique, email varchar2(25), salary number(8,2), hire_date date not null, constraint emp_email_uk unique(email) );

 

 

check 约束

check 约束用于对一个属性的值加以限制

在check 中定义检查的条件表达式，数据需要符合设置的条件

create table emp3( id number(4) primary key , age number(2) check(age >0 and age <100), salary number(7,2), sex char(1), constraint salary_check check(salary>0) )

 

在这种约束下，插入记录或修改记录时，系统要测试新的记录的值是否满足条件

 

关系模型的三类完整性规则

• 实体完整性规则 这条规则要求关系中在组成主键的属性上不能有空值

• 参照完整性规则 这条规则要求“不引用不存在的实体”

• 用户定义的完整性规则 反应了某一具体的应用涉及的数据必须满足的语义要求

外键约束(foreign key)

外键是表中的一个列,其值必须在另一表的主键或者唯一键中列出

作为主键的表称为"主表" ,作为外键的关系称为"依赖表"

外键参照的是主表的主键或者唯一键

对玉主表的删除和修改主键值的操作,会对依赖关系产生影响,以删除为例:当要删除主表的某个记录(即删除一个主键值,那么对依赖的影响可采取下列3种做法:

1. restrict方式 ：只有当依赖表中没有一个外键值与要删除的主表中主键值相对应时，才可执行删除操作

2. CASCADE方式:将依赖表中所有外键值与主表中要删除的主键值相对应的记录一起删除

3. SET NULL方式:将依赖表中所有与主表中被删除的主键值相对应的外键值设为空值

FOREIGN KEY (DEPTNO) REFERENCES DEPT (DEPTNO)

[ON DELETE [CASCADE ISET NULL]]如省略on短语,缺省为第一中处理方式。

 

约束的添加和撤销

可添加或删除约束，但不能直接修改

alter table tablename
增加
add constraint con_name unique(col)
删除
drop constraint com_name[cascade]

 

查询constraint

select constraint_name,constraint_type from user_constraints where table_name=upper('sxtstu05')

或者where owner ='SCOTT' 大写

select constraint_name,constraint_type from user_constraints where owner ='SCOTT'

select constraint_name,column_name from user_cons_columns where table_name =upper('tablename')