【Oracle】第11天 约束、注释、默认值、同义词

约束

定义

约束（CONSTRAINT）就是强加在数据表中的规则或者条件，如果录入不符合规则的数据，拒绝录入

分类

按照约束的内容不同分类

UN_唯一约束

字段中不能出现重复的非空数据，换言之，如果只是空值，可以重复

NN_非空约束

字段中不能出现空值

PK_主键约束

字段中既不能出现空值，也不能出现重复值

CK_检查约束

自定义约束内容（类似于WHERE中的条件）

FK_外键约束

字段的取值范围取决于另一张表的某字段

根据约束建立时是否随同字段一同声明分类

行级约束（列级约束）

声明字段名与字段属性的同时，紧随其后直接声明约束

表级约束

所有的字段与字段属性声明结束后，再单独声明约束

注意

1.同一用户下不能出现重复的约束名
2.表删除时，该表下的所有约束也会一并删除
3.若表中某字段上已存在违反约束的数据，则约束不能顺利建立
4.约束可以在表建完后再单独建立
5.一个约束可以建在一个字段上，也可建立在多个字段上

表删除时，特指DROP语句

6.若是唯一约束建立在多个字段上，以这些字段内容的组合是否重复来作为是否违反唯一约束的标准
7.建立了唯一约束的字段上会自动建立一个唯一索引
8.一个表中只能存在一个主键约束
9.建立了主键约束的字段，会自动建立一个唯一索引
10.条件中不允许写子查询
11.行级约束只能针对当前列，不能引用其他列的检查条件，表级约束则没有此限制
12.检查约束针对的是非空值
13.外键约束是建立在子表上的，而非主表
14.外键针对的是非空值，即空值不受外键约束限制
15.想要在子表上建立外键约束指向主表的字段，主表上被指定的字段必须是建立过主键约束或唯一约束
16.子表中若存在主表中不存在的数据，则建立外键时会失败
17.子表中想要插入或修改数据，处理后的数据必须存在于主表中，否则会处理失败
18.主表中已存在于子表中的内容，想要删除或修改时必须先删除或修改对应的子表内容，否则会处理失败
19.外键因其独特而顽固的限制，很少在项目中被使用，甚至有些项目组会禁止使用外键

语法

建立一个唯一约束

已经建立完表的基础上建立唯一约束

ALTER TABLE TB_NAME ADD CONSTRAINT CONSTR_NAME UNIQUE(COL_NAME);

建立表时同时建立约束

CREATE TABLE TB_NAME(
COL_NAME1 COL_TYPE UNIQUE,--行级约束并且系统自动分配约束名称
COL_NAME2 COL_TYPE,
...
COL_NAMEN COL_TYPE );

CREATE TABLE TB_NAME(
COL_NAME1 COL_TYPE CONSTRAINT CONSTR_NAME UNIQUE
COL_NAME2 COL_TYPE
...
COL_NAMEN COL_TYPE);

CREATE TABLE TB_NAME(
COL_NAME1 COL_TYPE 
COL_NAME2 COL_TYPE
...
COL_NAMEN COL_TYPE,
CONSTRAINT CONSTR_NAME UNIQUE(COL_NAME1,COL_NAME2...));

建立一个非空约束

在已经建立好表的基础上建立或者取消非空约束

ALTER TABLE TB_NAME MODIFY COL_NAME [NOT] NULL;

在建立表的时候同时建立非空约束

CREATE TABLE TB_NAME(
COL_NAME1 COL_TYPE NOT NULL,--行级约束并且系统自动分配约束名称
COL_NAME2 COL_TYPE,
...
COL_NAMEN COL_TYPE );

CREATE TABLE TB_NAME(
COL_NAME1 COL_TYPE CONSTRAINT CONSTR_NAME NOT NULL
COL_NAME2 COL_TYPE
...
COL_NAMEN COL_TYPE);

注意：
非空约束只有行级约束，没有表级约束

建立一个主键约束

在建立表的基础上建立一个主键约束

ALTER TABLE TB_NAME ADD CONSTRAINT CONSTR_NAME PRIMARY KEY(COL_NAME);

在建立表的同时建立一个主键约束

CREATE TABLE TB_NAME(
COL_NAME1 COL_TYPE PRIMARY KEY,--行级约束并且系统自动分配约束名称
COL_NAME2 COL_TYPE,
...
COL_NAMEN COL_TYPE );

CREATE TABLE TB_NAME(
COL_NAME1 COL_TYPE CONSTRAINT CONSTR_NAME PRIMARY KEY
COL_NAME2 COL_TYPE
...
COL_NAMEN COL_TYPE);

CREATE TABLE TB_NAME(
COL_NAME1 COL_TYPE 
COL_NAME2 COL_TYPE
...
COL_NAMEN COL_TYPE,
CONSTRAINT CONSTR_NAME PRIMARY KEY(COL_NAME));

建立一个检查约束

建表后设立检查约束

ALTER TABLE TB_NAME ADD CONSTRAINT CONSTR_NAME CHECK(CONDITION)

建表时同时建立检查约束

CREATE TABLE TB_NAME(
COL_NAME1 COL_TYPE CHECK(CONDITION..),--行级约束并且系统自动分配约束名称
COL_NAME2 COL_TYPE,
...
COL_NAMEN COL_TYPE );

CREATE TABLE TB_NAME(
COL_NAME1 COL_TYPE CONSTRAINT CONSTR_NAME CHECK(CONDITION)
COL_NAME2 COL_TYPE
...
COL_NAMEN COL_TYPE);

CREATE TABLE TB_NAME(
COL_NAME1 COL_TYPE 
COL_NAME2 COL_TYPE
...
COL_NAMEN COL_TYPE,
CONSTRAINT CONSTR_NAME CHECK(CONDITION));

建立一个外键约束

在已经建立表的基础上建立一个外键约束

ALTER TABLE TB_NAME ADD CONSTRAINT CONSTR_NAME FOREIGN KEY(COL_NAME) REFERENCES MAIN_TB_NAME(PK_COL_NAME OR UN_COL_NAME) 

在建立表的同时建立一个外键约束

CREATE TABLE TB_NAME (
COL_NAME1 COL_TYPE REFERENCES MAIN_TB_NAME(PK_COL_NAME OR UN_COL_NAME)
,COL_NAME2 COL_TYPE
... );

CREATE TABLE TB_NAME(
COL_NAME1 COL_TYPE CONSTRAINT CONSTR_NAME FOREIGN KEY(COL_NAME) REFERENCES MAIN_TB_NAME (PK_COL_NAME OR UN_COL_NAME)
,COL_NAME2 COL_TYPE
...);

CREATE TABLE TB_NAME (
COL_NAME1 COL_TYPE 
,COL_NAME2 COL_TYPE
...
,COL_NAMEN COL_TYPE
CONSTRAINT CONSTR_NAME FOREIGN KEY(COL_NAME) REFERENCES MAIN_TB_NAME(PK_COL_NAME OR UN_COL_NAME));

删除一个约束

ALTER TABLE TB_NAME DROP CONSTRAINT CONSTR_NAME ;

激活或禁用一个约束

--禁用
ALTER TABLE TB_NAME DISABLE CONSTRAINT CONSTR_NAME ;
--激活
ALTER TABLE TB_NAME ENABLE CONSTRAINT CONSTR_NAME ;

局限性

• 因为约束的存在，数据库进行DML操作时会额外花费时间用来做数据的验证，且约束不能为查询带来效率上的提升，所以约束的地位渐渐被代码验证的方式所取代
• 使用CREATE TABLE TB_NAME AS SELECT …语法建立一张表时，除了非空，其他的约束都会被忽视

注释

定义

对表本身或表中的每个字段添加一段解释说明，使得初次接触该表的开发人员能尽快了解该表

体现

1.表结构描述中
2.查询结果某行数据纵向展示时

按住Ctrl右击表名→选择“Column”选项卡→查看或直接修改注释

语法

添加一个列注释

COMMENT ON COLUMN TB_NAME.COL_NAME IS '注释内容';

修改一个列注释

COMMENT ON COLUMN TB_NAME.COL_NAME IS '修改后的注释内容';

删除一个列注释

COMMENT ON COLUMN TB_NAME.COL_NAME IS '';

添加一个表注释

COMMENT ON TABLE TB_NAME IS  '注释内容';

注意

1.注释内容是字符型的，注意加英文单引号
2.删除注释时用的‘’不能替换为NULL
3.添加注释时只能一个一个添加
4.使用CREATE TABLE TB_NAME AS SELECT … 创建表时注释会被忽略

数据字典

• 表注释信息
  USER_TAB_COMMENTS
• 列注释信息
  USER_COL_COMMENTS

默认值

语法

在建表时设置默认值

CREATE TABLE TB_NAME (
COL_NAME1 COL_TYPE DEFAULT DEFAULT_VALUE
,COL_NAME2 COL_TYPE DEFAULT DEFAULT_VALUE 
...);

已经建表时设置默认值

ALTER TABLE TB_NAME MODIFY COL_NAME DEFAULT DEFAULT_VALUE; 

删除默认值

ALTER TABLE TB_NAME MODIFY COL_NAME DEFAULT NULL;

同义词

定义

相当于给数据库对象（表、索引、视图）（不能给列起）取一个长期有效的别名

分类

• 公有同义词：创建后所有用户都可以使用的
• 私有同义词：只能由创建了这个同义词的用户或者被授权的用户使用

赋权

私有同义词

GRANT CREATE|DROP SYNONYM TO USER;

共有同义词

GRANT CREATE PUBLIC SYNONYM TO USER;

给PUBLIC 组赋予权限

GRANT SELECT ON USER_NAMED.TB_NAME TP PUBLIC;

语法

创建一个表的同义词

CREATE [PUBLIC] SYNONYM S_NAME FOR TB_NAME;

注意：

1）公有同义词再创建完成后需要赋予原对象的查询权限给PUBLIC组，否则不含有原对象查询权限的用户无法使用该公有同义词
2）公有同义词使用时，直接使用同义词即可；私有同义词使用时必须加上"用户名."