一、约束
1、、约束分类
(1)、非空约束(not null)
在创建表时创建:column_name data_type [constraint constraint_name] not null --只能在列级指定,不能在表级。
在更改表时创建:alter table table_name modify column_name not null;
(2)、唯一约束(unique):要求不能重复,可以为空,但只能一行为空或者几个组合列的一行全部为空
在创建表时创建:列级:create table table_name(column_name data_type [constraint constraint_name] unique [, ...] [index tablespace tablespace_name])
表级:create table table_name(column_name data_type [, ....],[constraint constraint_name] unique(column_name [, ...]) [index tablespace tablespace_name])
在更改表时创建:alter table table_name add constraint constraint_name unique(column_name [, ....]);
( 3)、主键约束(primary key):要求不能重复且非空。当创建主键约束时,自动创建唯一索引,可以指定存储索引的表空间
在创建表时创建约束:列级:create table table_name (column_name data_type [constraint constraint_name] primary key [index tablespace tablespace_name] [, ...])
表级:create table table_name(
column_name data_type
[, ....],
constraint constraint_name primary key(column_name [, ...]) [index tablespace tablespace_name]
[, ...]
)
在更改表时创建约束:alter table table_name add constraint constraint_name primary key(column_name [, ...]) [index tablespace tablespace_name];
( 4)、检查约束(check):要求莫一列或者某些列符合指定的条件。
在创建表示创建约束:表级:create table table_name (
column_name data_type [constraint constraint_name] check(条件)
[, ....]
);
列级:create table table_name(
column_name data_type
[, ....],
[constraint constraint_name] check (条件)
[, ....]
);
在更改表时创建约束:alter table table_name add [constraint constraint_name] check(条件);
( 5)、外键约束(foreign key):某个表的一列或者多列引用同一个表或者其他表的一列或者多列,被引用的列上必须创建主键或者唯一约束。要引用其他模式的表中的列,用户在相应的列上需要有references权限。
在创建表时创建约束:表级:create table table_name(
column_name data_type [constraint constraint_name] [foreign key] references schema.table_name(column_name)
[, ....]
);
列级:create table table_name(
column_name data_type
[, ....],
[constraint constraint_name] foreign key(column_name [, ....]) references [schema. ]table_name(column_name [, ....])
[, ....]
);
在更改表时创建约束:alter table table_name add constraint constraint_name foreign key(column_name [, ....]) references [schema.]table_name(column_name [, ....])
2、约束状态:enable:启用 当使用enable时,如果没有指定validate或者novalidate,则默认为validate
disable:禁用 当使用disable时,如果没有指定validate或者novalidate,则默认为novalidate
valiate:检查表中已经存在的老数据。
novalidate:不检查表中已经存在的老数据。
可以在创建约束时指定约束的状态,比如alter table table_name add constraint constraint_name foreign key(column_name [, ....]) references [schema.]table_name(column_name [,....])
enable validate;
也可以在创建约束之后更改约束的状态,比如:alter table table_name disable validate constraint constraint_name;
如果更改主键约束的状态,可以直接使用primary key,而不必使用约束名。alter table table_name enable validate primary key;
当禁用主键或者唯一约束时,自动创建的唯一索引会被删除。
当存在引用关系时,被引用的列上的主键或者唯一约束不能禁用
3、延迟与立即约束
约束有不可延迟(not defferrable)和可延迟(defferrable)两种,默认为不可延迟。如果想创建可延迟的约束,必须在创建约束时指定defferrable。
defferrable有两个选项:initially deffered和initially immedaite,默认为initially immediate
对于可延迟的约束,可以在以后在defferred和immediate之间更改 set constraint constraint_name immediate;
4、删除约束
alter table table_name drop constraint constraint_name;
对于主键约束,可以直接使用primary 可以删除 如:alter table table_name drop primary key;
当存在引用关系,被引用的列上的主键或者唯一约束不能删除,即便外键被禁用。
二、同义词
私有同义词:私有同义词属于某个模式的对象,其他用户使用需要在同义词前加模式名最为前缀
用户可以通过同义词访问其他对象,但必须要有相应的权限。
创建:需要create synonym权限,为其他模式创建私有同义词需要create any synonym权限
create synonym synonym_name for [schema.]object_name;
删除:drop synonym [schema.]synonym_name;
删除其他模式需要drop any synonym权限
共有同义词:共有同义词属于public,用户可以通过共有同义词直接访问其他对象,不需要加模式名最为前缀,但也必须有相应的权限。
创建:需要create public synonym 权限
create public synonym name for [schema. ]object_name;
删除:需要drop public synonym权限
drop public synonym name;
更改当前模式:如果用户需要经常访问某个模式的对象,且没有同义词,需要经常加该模式的模式名作为对象的前缀,可以考虑更改当前模式。切换为其他模式,
并不意味着用户可以随便放该模式的对象,用户仍然需要相应的权限。
alter session set current_schema='schema_name';
三、序列
创建序列:create sequence seq_name
start with a --初始值,默认为1
increment by b --递增值,默认为1;可以为负值,若为负值,则序列递减。
minvalue c --最小值。increment by的值为正,默认最小值为初始值,默认没有最大值。没有最小值为nominvalue
maxvalue d --最大值。increment by 的值为负,默认最大值为初始值,默认没有最小值。没有最大值为nomaxvalue
cache n --在缓存中存储的数量,默认为20。如果太小,oracle需要频繁读取,降低性能;如果太大,oracle崩溃时丢失的多。
cycle --是否循环,默认为非循环:nocycle。如果为cycle,increment by 的为正值时,必须设置最大值,increment by 的值为负,则必须设置最小值。
使用序列:nextval,currval --在每个会话中必须先使用nextval,然后才可以使用currval。
select sequence_name.nextval from dual;
insert into table_name( ....)
values(sequence_name.nextval,.....);
update table_name set column_name=sequence_name.nextval
--要使用序列,必须对序列有select权限。
序列不连续问题:1、oracle读取序列到内存,系统崩溃之后导致序列丢失
2、使用序列之后,事务被撤销,也会导致序列值的不连续。
删除序列:drop sequenc esequence_name;