oracle必看经典.docx
- 文档编号:29894790
- 上传时间:2023-08-03
- 格式:DOCX
- 页数:17
- 大小:22.88KB
oracle必看经典.docx
《oracle必看经典.docx》由会员分享,可在线阅读,更多相关《oracle必看经典.docx(17页珍藏版)》请在冰豆网上搜索。
oracle必看经典
oracle 建立主键与索引
(2008-05-0411:
24:
39)
转载
标签:
it
oracle
主键
索引
index
unique
primary
key
分类:
工作
创建主键:
altertableTaddprimarykey(V)
T是表名,V是列名
创建索引:
createindexF2009100000NMINFOSYS_XIANGonf2009100000nminfo(SYS_XIANG);
创建一般索引,索引名为表名+列名
createuniqueindexF2009100000NMINFOSYS_ZDMonf2009100000nminfo(SYS_ZDM);
创建唯一索引
createBITMAPindexF2009100000NMINFOSYS_XIANGonf2009100000nminfo(SYS_XIANG);
创建位图索引
完整语法如下:
CREATE(UNIQUE|BITMAP)INDEX[用户名.]索引名ON[用户名.]表名(列名[ASC|DESC],[列名[ASC|DESC]]...)
[TABLESPACE表空间名]
[ PCTFREE 正整型数 ]
[ INITRANS 正整型数 ]
[ MAXTRANS 正整型数 ]
[存储子句]
[ LOGGING| NOLOGGING ]
[ NOSORT ]
Oracle中怎么知道表没有建立主键?
一般的情况下,表的主键是必要的,没有主键的表可以说是不符合设计规范的。
SELECTtable_nameFROMUser_tablest WHERENOTEXISTS
(SELECTtable_nameFROMUser_constraintscWHEREconstraint_type='P'ANDt.table_name=c.table_name)
其它相关数据字典解释
user_tables 表
user_tab_columns 表的列
user_constraints 约束
user_cons_columns 约束与列的关系
user_indexes 索引
Oracle建立自增主键
首先,你要有一张表!
CREATETABLEexample(
IDNumber(4)NOTNULLPRIMARYKEY,
NAMEVARCHAR(25),
PHONEVARCHAR(10),
ADDRESSVARCHAR(50));
如果对于以上的建表语句还有疑问的话,建议您不要继续了!
有那么些时间您还不如去看看金庸读读琼瑶!
然后,你需要一个自定义的sequence
CREATESEQUENCEemp_sequence
INCREMENTBY1--每次加几个
STARTWITH1--从1开始计数
NOMAXVALUE--不设置最大值
NOCYCLE--一直累加,不循环
NOCACHE--不建缓冲区
以上代码完成了一个序列(sequence)的建立过程,名称为emp_sequence,范围是从1开始到无限大(无限大的程度是由你机器决定的),nocycle是决定不循环,如果你设置了最大值那么你可以用cycle会使seq到最大之后循环.对于nocache顺便说一下如果你给出了cache值那么系统将自动读取你的cache值大小个seq
这样在反复操作时会加快运行速度,但如果遭遇意外情况如当机了或oracle死了,则下次取出的seq值将和上次的不连贯.(如果连不连贯无所谓建议用cache,因为时间就是金钱呀!
跑题了!
)
书接上文,你只有了表和序列还不够,还需要一个触发器来执行它!
代码如下:
CREATETRIGGER"触发器名称"BEFORE
INSERTONexampleFOREACHROWWHEN(new.idisnull)
begin
selectemp_sequence.nextvalinto:
new.idfromdual;
end;
打完收工!
下面你就试试插入数据吧!
INSERTINTOexample(Name,phone,address)Values('Cao','56498543','Heibei');
PrimaryKey与UniqueKey
Primarykey与UniqueKey都是唯一性约束。
但二者有很大的区别:
1、Primarykey的1个或多个列必须为NOTNULL,如果列为NULL,在增加PRIMARYKEY时,列自动更改为NOTNULL。
而UNIQUEKEY对列没有此要求。
2、一个表只能有一个PRIMARYKEY,但可以有多个UNIQUEKEY。
下面以测试说明:
SQL>createtablet(aint,bint,cint,dint);
Tablecreated.
SQL>desct
Name Null?
Type
------------------------------------------------------------
A NUMBER(38)
B NUMBER(38)
C NUMBER(38)
D NUMBER(38)
SQL>altertabletaddconstraintpk_tprimarykey(a,b);
Tablealtered.
SQL>desct
Name Null?
Type
-----------------------------------------------------------------
A NOTNULLNUMBER(38)
B NOTNULLNUMBER(38)
C NUMBER(38)
D NUMBER(38)
可以看到A、B两个列都自动改为了NOTNULL
SQL>altertabletmodify(aintnull);
altertabletmodify(aintnull)
*
ERRORatline1:
ORA-01451:
columntobemodifiedtoNULLcannotbemodifiedtoNULL
可以看到,列A不允许改为NULL
SQL>altertabletdropconstraintpk_t;
Tablealtered.
SQL>altertabletaddconstraintuk_t_1unique(a,b);
Tablealtered.
SQL>desct
Name Null?
Type
------------------------------------------------------------
A NUMBER(38)
B NUMBER(38)
C NUMBER(38)
D NUMBER(38)
我们看到列A又变回了NULL。
注意到,在删除主键时,列的NULLABLE会回到原来的状态。
如果在创建主键后,对原来为NULL的主键列,显式设为NOTNULL,在删除主键后仍然是NOTNULL。
比如在创建主键后,执行下面的操作,可以看到:
SQL>altertabletmodify(bintnotnull);
Tablealtered.
SQL>altertabletdropconstraintpk_t;
Tablealtered.
SQL>desct
Name Null?
Type
-----------------------------------------------------------
A NUMBER(38)
B NOTNULLNUMBER(38)
C NUMBER(38)
D NUMBER(38)
再做如下的实验:
SQL>droptablet;
Tabledropped.
SQL>createtablet(aint,bint,cint,dint);
Tablecreated.
SQL>altertabletaddconstraintuk_t_1unique(a,b);
Tablealtered.
SQL>altertabletaddconstraintuk_t_2unique(c,d);
Tablealtered.
可以看到可以增加两个UNIQUEKEY。
看看能不能增加两个主键:
SQL>altertabletaddconstraintpk_tprimarykey(c);
Tablealtered.
SQL>altertabletaddconstraintpk1_tprimarykey(d);
altertabletaddconstraintpk1_tprimarykey(d)
*
ERRORatline1:
ORA-02260:
tablecanhaveonlyoneprimarykey
由此可以看到一个表只能有一个主键。
SQL>altertabletdropconstraintpk_t;
Tablealtered.
SQL>insertintot(a,b)values(null,null);
1rowcreated.
SQL>/
1rowcreated.
SQL>insertintot(a,b)values(null,1);
1rowcreated.
SQL>/
insertintot(a,b)values(null,1)
*
ERRORatline1:
ORA-00001:
uniqueconstraint(SYS.UK_T_1)violated
SQL>insertintot(a,b)values(1,null);
1rowcreated.
SQL>/
insertintot(a,b)values(1,null)
*
ERRORatline1:
ORA-00001:
uniqueconstraint(SYS.UK_T_1)violated
主键和唯一键约束是通过参考索引实施的,如果插入的值均为NULL,则根据索引的原理,全NULL值不被记录在索引上,所以插入全NULL值时,可以有重复的,而其他的则不能插入重复值
Oracle数据库设计——定义约束主键1
声明约束
主键(PRIMARYKEY)
一张表不一定有主键,但大多数表都创建了主键,主键值必须唯一并且组成主键的各列都不能为空。
想象一下存储学生信息的一张表。
在学生表(STUDENTS)每个学生有且仅有一行记录。
因此,在STUDENTS表中每一行只能确定一个且仅有一个学生。
举例来说,一个处理学生学费清单的应用中,每个学生只能有一张且仅有一张学费单
除了商业强制规则,还有其他数据库要求,主键是保证参照完整性的父子关系的一部分。
另外,主键需要一个索引(Index),一块物理硬盘空间,主要是提供快速的数据访问能力
Oracle数据库引擎强制主键约束规则,在创建主键时,首先,所有的主键值都是唯一的,第二,它们必须有一个值,也就是说,作为主键的列不能为NULL
主键约束执行可以暂时的disabled,然后再enabled.使用ALTERTABLE语句来完成,约束选项可为:
DISABLE或ENABLE.一个原因是在加载数据时disable约束可以减少加载时间,disable约束将减少数据加载时间是因为索引被disabled了,因此索引不需要更新(notupdate)。
在一个事务中,一个应用能够暂时的挂起约束执行。
这里一个程序开始一个事务通过设置约束为延缓状态。
数据加载后有一个commit,然后,约束重新应用于Oracle.这个选项要求约束使用DEFERRABLE关键字创建。
你可以向一个表中加载数据时使约束disable,加载完后,enable约束,约束规则仅应用于新加载的数据。
旧的数据,即使违反商业规则,仍保留在数据表中,这个面向商业规则强制性策略可以应用于数据仓库,它必须有可供分析的历史数据。
这个选项要求enable约束时使用NOVALIDATE关键字
几种类型的主键强制性,比如DEFERRABLE和NOVALIDATE,将影响使用主键约束的索引类型.这些选项将使用nonunique索引,一个常规主键约束,从来不试图违反它,甚至临时的在一个事务内,使用一个唯一索引。
事实上,一个主键的表存储在数据字典内,我们看一下数据字典视图USER_TAB_COLUMNS,它是一个查看一个表内字段名的字典视图。
我们也有约束的视图,是USER_CONSTRAINTS和USER_CONS_COLUMNS,这些视图展示哪些表有约束,约束名、类型和状态
创建主键约束
这部分中将会使用一个示例表:
TEMP,表结构如下:
Name Null?
Type
ID VARCHAR2(5)
NO NUMBER
创建主键有几种方式:
1、列约束子句
2、表约束子句
3、ALTERTABLE语句
下面讨论创建主键约束的三种格式技巧和关系。
其他类型的约束,Unique,Foreignkey,check,也可以使用每种方式创建
列约束子句
下面创建一张表,有两个字段,字段id为主键,这是一个列约束子句的例子。
CREATETABLEtemp(
idVARCHAR2(5)PRIMARYKEY,
noNUMBER
);
也可以为主键约束指定名称,
CREATETABLEtemp(
idVARCHAR2(5)CONSTRAINTPRIMARYKEYmy_constraint_name,
noNUMBER
);
表约束子句
表约束子句是在表定义结尾定义主键约束。
表约束子句是CREATETABLE语句的一部分,如果约束子句有语法错误,整个语句将失败,表不会被创建。
下面以模板方式举例说明,Createtable语句声明了一个主键,表约束子句允许包括多个约束,使用“,”分隔每一个约束定义。
CREATETABLEtemp
(id VARCHAR2(5),
no NUMBER,
CONSTRAINTPRIMARYKEY(id),
CONSTRAINT..nextconstraint,
CONSTRAINT..nextconstraint)TABLESPACEetc;
下面创建一个TEMP表,使用表约束子句。
CREATETABLEtemp
(id VARCHAR2(5),
no NUMBER,
CONSTRAINTPRIMARYKEY(id))TABLESPACEstudent_data;
ALTERTABLE语句
altertable语句是另外一个管理约束的选择。
一旦你创建了一张表,你可以使用altertable语句管理约束、增加列、改变存储参数。
执行功能 ALTER语法
Addaconstraint ALTERTABLEtable_nameADDCONSTRAINTetc
Dropaconstraint ALTERTABLEtable_nameDROPCONSTRAINTetc
Disableaconstraint ALTERTABLEtable_nameDISABLECONSTRAINTetc
Enableaconstraint ALTERTABLEtable_nameENABLECONSTRAINTetc
下面DDL包含两个DDL语句:
Createtable语句和Altertable语句。
主键名为:
PK_TEMP
CREATETABLEtemp(
id VARCHAR2(5),
no NUMBER
);
ALTERTABLEtempADDCONSTRAINTpk_tempPRIMARYKEY(id);
Altertable命令有许多选项,一个记住语法的方法是Oracle执行这个操作需要的信息:
1)、你不得不告诉Oracle你准备alter什么表:
Altertabletable_name
2)、然后,你准备做什么?
Adding一个约束
ALTERTABLEtable_nameADDCONSTRAINT
3)、强烈建议但不要求为约束定义个名字。
约束名不需要放在引号里,但会以大写字母形式存储在数据字典里,
ALTERTABLEtempADDCONSTRAINTpk_temp
4)、表示约束类型将是PrimaryKey,Unique,ForeignKey,或Check约束
ALTERTABLEtempADDCONSTRAINTpk_tempPRIMARYKEY
5)、跟在约束类型后面有几种特殊选项,PrimaryKey和Unique约束,需要指定约束的列;Check约束需要指定约束规则。
ALTERTABLEtempADDCONSTRAINTpk_tempPRIMARYKEY(ID);
6)、PrimaryKey和Unique约束应当指定索引的表空间名,使用USINGINDEXTABLESPACE子句.
ALTERTABLEtempADDCONSTRAINTpk_tempPRIMARYKEY(ID)USINGINDEXTABLESPACEstudent_index;
命名约束
主键命名一般有两种格式:
1.PK_table_name
2.table_name_PK
创建主键不一定非要给主键定义个名字,如果不指定主键名,oracle会自动生成一个主键名,存储在数据字典中,但如果出现逐渐冲突,查询起来没有直接定义主键名方便,如果主键名定义为:
PK_TEMP,就可以迅速的知道问题的所在:
TEMP表上的主键出现冲突。
主键索引(ThePrimaryKeyIndex)
当创建一个主键时,oracle会自动在作为主键的列上创建一个主键索引。
如果索引已存在,则oracle利用已存在的索引。
索引是主键完整的一部分。
根据主键选项,索引可以是Unique也可以不是。
可延期的主键约束使用nonunique索引。
索引不用来增强主键的商业规则,但索引还是需要的。
索引的好处是对表的查询,如果主键不可用,索引被删掉了,查询性能就非常的差。
表占用物理存储空间,索引也需要。
创建主键时需要为索引指定一个表空间。
因为I/O争夺和索引增长不同于表,我们总是把索引放在洋单独的表空间中。
下面Altertable语句创建一个主键,并使用Usingindextablespace关键字为索引镇定表空间。
CREATETABLEstudents
(student_id VARCHAR2(10),
student_name VARCHAR2(30),
college_majorVARCHAR2(15),
status VARCHAR2(20))TABLESPACEstudent_data;
ALTERTABLEstudents
ADDCONSTRAINTpk_studentsPRIMARYKEY(student_id)
USINGINDEXTABLESPACEstudent_index;
如果不指定表空间,Oracle使用默认表空间里创建索引。
所有的oracle用户都可以在默认表空间中创建索引。
没有指定表空
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- oracle 经典
![提示](https://static.bdocx.com/images/bang_tan.gif)