Oracle之主键的创建、添加、删除操作

最新推荐文章于 2022-03-19 17:01:39 发布
最新推荐文章于 2022-03-19 17:01:39 发布
阅读量487 收藏 1
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/Richardzhu/p/3470929.html
版权

一、创建表的同时创建主键约束

    1.1、无命名

SQL> create table jack (id int primary key not null,name varchar2(20));
 
Table created
 
SQL> select table_name,index_name from user_indexes where table_name='JACK';
 
TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
JACK                           SYS_C0011100

    1.2、有命名

SQL> create table jack (id int ,name varchar2(20),constraint ixd_id primary key(id));
 
Table created
 
SQL> select table_name,index_name from user_indexes where table_name='JACK';
 
TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
JACK                           IXD_ID


二、向表中添加主键约束

SQL> create table jack as select * from dba_objects;
 
Table created
 
SQL> desc jack;
Name           Type          Nullable Default Comments 
-------------- ------------- -------- ------- -------- 
OWNER          VARCHAR2(30)  Y                         
OBJECT_NAME    VARCHAR2(128) Y                         
SUBOBJECT_NAME VARCHAR2(30)  Y                         
OBJECT_ID      NUMBER        Y                         
DATA_OBJECT_ID NUMBER        Y                         
OBJECT_TYPE    VARCHAR2(19)  Y                         
CREATED        DATE          Y                         
LAST_DDL_TIME  DATE          Y                         
TIMESTAMP      VARCHAR2(19)  Y                         
STATUS         VARCHAR2(7)   Y                         
TEMPORARY      VARCHAR2(1)   Y                         
GENERATED      VARCHAR2(1)   Y                         
SECONDARY      VARCHAR2(1)   Y                         
NAMESPACE      NUMBER        Y                         
EDITION_NAME   VARCHAR2(30)  Y                         
 
SQL> alter table jack add constraint pk_id primary key(object_id);
 
Table altered
 
SQL> select table_name,index_name from user_indexes where table_name='JACK';
 
TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
JACK                           PK_ID

----另外当索引创建好以后再添加主键的效果：

SQL> create table jack as select * from dba_objects;
 
Table created
 
SQL> create index ind_object_id on jack(object_id);
 
Index created
 
SQL> select table_name,index_name from user_indexes where table_name='JACK';
 
TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
JACK                           IND_OBJECT_ID
 
SQL> desc jack;
Name           Type          Nullable Default Comments 
-------------- ------------- -------- ------- -------- 
OWNER          VARCHAR2(30)  Y                         
OBJECT_NAME    VARCHAR2(128) Y                         
SUBOBJECT_NAME VARCHAR2(30)  Y                         
OBJECT_ID      NUMBER        Y                         
DATA_OBJECT_ID NUMBER        Y                         
OBJECT_TYPE    VARCHAR2(19)  Y                         
CREATED        DATE          Y                         
LAST_DDL_TIME  DATE          Y                         
TIMESTAMP      VARCHAR2(19)  Y                         
STATUS         VARCHAR2(7)   Y                         
TEMPORARY      VARCHAR2(1)   Y                         
GENERATED      VARCHAR2(1)   Y                         
SECONDARY      VARCHAR2(1)   Y                         
NAMESPACE      NUMBER        Y                         
EDITION_NAME   VARCHAR2(30)  Y                         
 
SQL> alter table jack add constraint pk_id primary key(object_id);
 
Table altered
 
SQL> desc jack;
Name           Type          Nullable Default Comments 
-------------- ------------- -------- ------- -------- 
OWNER          VARCHAR2(30)  Y                         
OBJECT_NAME    VARCHAR2(128) Y                         
SUBOBJECT_NAME VARCHAR2(30)  Y                         
OBJECT_ID      NUMBER                                  
DATA_OBJECT_ID NUMBER        Y                         
OBJECT_TYPE    VARCHAR2(19)  Y                         
CREATED        DATE          Y                         
LAST_DDL_TIME  DATE          Y                         
TIMESTAMP      VARCHAR2(19)  Y                         
STATUS         VARCHAR2(7)   Y                         
TEMPORARY      VARCHAR2(1)   Y                         
GENERATED      VARCHAR2(1)   Y                         
SECONDARY      VARCHAR2(1)   Y                         
NAMESPACE      NUMBER        Y                         
EDITION_NAME   VARCHAR2(30)  Y                         
 
SQL> select table_name,index_name from user_indexes where table_name='JACK';
 
TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
JACK                           IND_OBJECT_ID

三、修改主键约束

    3.1、禁用/启用主键

SQL> select a.table_name,a.index_name,b.constraint_name,b.constraint_type,b.status from user_indexes a,user_constraints b where b.table_name='JACK'and a.table_name=b.table_name;
 
TABLE_NAME                     INDEX_NAME                     CONSTRAINT_NAME                CONSTRAINT_TYPE STATUS
------------------------------ ------------------------------ ------------------------------ --------------- --------
JACK                           PK_ID                          PK_ID                          P               ENABLED
 
SQL> alter table jack disable primary key;
 
Table altered
 
SQL> select a.table_name,a.index_name,b.constraint_name,b.constraint_type,b.status from user_indexes a,user_constraints b where b.table_name='JACK'and a.table_name=b.table_name;
 
TABLE_NAME                     INDEX_NAME                     CONSTRAINT_NAME                CONSTRAINT_TYPE STATUS
------------------------------ ------------------------------ ------------------------------ --------------- --------
 
SQL> alter table jack enable primary key;
 
Table altered
 
SQL> select a.table_name,a.index_name,b.constraint_name,b.constraint_type,b.status from user_indexes a,user_constraints b where b.table_name='JACK'and a.table_name=b.table_name;
 
TABLE_NAME                     INDEX_NAME                     CONSTRAINT_NAME                CONSTRAINT_TYPE STATUS
------------------------------ ------------------------------ ------------------------------ --------------- --------
JACK                           PK_ID                          PK_ID                          P               ENABLED

    3.2、重命名主键

SQL> select a.table_name,a.index_name,b.constraint_name,b.constraint_type,b.status from user_indexes a,user_constraints b where b.table_name='JACK'and a.table_name=b.table_name;
 
TABLE_NAME                     INDEX_NAME                     CONSTRAINT_NAME                CONSTRAINT_TYPE STATUS
------------------------------ ------------------------------ ------------------------------ --------------- --------
JACK                           PK_ID                          PK_ID                          P               ENABLED
 
SQL> alter table jack rename constraint pk_id to pk_jack_id;
 
Table altered
 
SQL> select a.table_name,a.index_name,b.constraint_name,b.constraint_type,b.status from user_indexes a,user_constraints b where b.table_name='JACK'and a.table_name=b.table_name;
 
TABLE_NAME                     INDEX_NAME                     CONSTRAINT_NAME                CONSTRAINT_TYPE STATUS
------------------------------ ------------------------------ ------------------------------ --------------- --------
JACK                           PK_ID                          PK_JACK_ID                     P               ENABLED

四、删除表中已有的主键约束

    4.1、无命名

----先利用user_cons_columns表查得主键名：
SQL> select owner,constraint_name,table_name,column_name from user_cons_columns where table_name = 'JACK';
 
OWNER                          CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------ ----------------
JACK                           SYS_C0011105                   JACK                           ID
SQL> select table_name,index_name from user_indexes where table_name='JACK';
 
TABLE_NAME                     INDEX_NAME
------------------------------ ------------------------------
JACK                           SYS_C0011105
SQL> alter table jack drop constraint SYS_C0011105;
 
Table altered

    4.2、有命名

SQL> select owner,constraint_name,table_name,column_name from user_cons_columns where table_name = 'JACK';
 
OWNER                          CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------
JACK                           IXD_ID                         JACK                           ID
 
SQL> alter table jack drop constraint IXD_ID;
 
Table altered
 
SQL> select owner,constraint_name,table_name,column_name from user_cons_columns where table_name = 'JACK';
 
OWNER                          CONSTRAINT_NAME                TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------

 

转载于:https://www.cnblogs.com/Richardzhu/p/3470929.html

weixin_30677475
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle中创建自增主键的方法
03-17
oracle中创建自增主键的方法
oracle中添加删除主键的方法
09-09
本篇文章将详细介绍如何在Oracle中添加和删除主键。 首先，我们来探讨如何在创建表时同时设置主键约束： 1. **创建表的同时创建主键约束**： - **无命名**：当你创建一个新表时，可以直接在字段定义中指定该字段...
ORACLE创建自增长主键
socia1ca3t的博客
05-23 462
drop table INSTITUTION_CATEGORY; create table INSTITUTION_CATEGORY( ID number(5) not null primary key, NAME varchar2(50) not null, PARENT_ID number(5), IS_VALID varchar2(2...
Oracle新建主键自增表
sunansunan3222的博客
05-31 299
oracle创建主键自增表
oracle快速创建主键
08-23 247
oracle中，有时我们会发现有一些表中，一些记录它们每个字段的数据 都是一样一样的，即重复数据，这种数据的不存在肯定是不对了。 究其原因，就是该表没有主键，给一个表创建主键，非常容易： alter table student add constraint pk_student primary key(studentid); 但是如果这表的数据量特别的大，那个创建主键...
Oracle 创建主键自增表示例代码
12-16
- Oracle中可以使用`ALTER TABLE`语句来修改表结构，例如添加或删除字段，以及修改主键。 - 删除表字段时，需要确保该字段没有被其他对象引用，否则会导致依赖问题。 - 表的重命名使用`RENAME`语句，主键的增删改...
Oracle数据库中创建自增主键的实例教程
09-10
下面将详细介绍如何在Oracle中创建自增主键。 1. **创建序列（Sequence）**： 序列是Oracle提供的一种机制，用于生成一个唯一的、递增的数字序列。在这个实例中，我们可以创建一个名为`SEQ_STUDENT`的序列，设定...
c# oracle创建实体类操作
最新发布
03-04
通过EF，我们可以创建模型类，这些类映射到数据库表，然后使用这些类进行CRUD（创建、读取、更新、删除）操作。 2. Oracle Entity Framework支持： 虽然Entity Framework最初是为SQL Server设计的，但也可以与...
ORACLE新建表，删除表，设置主键，查询的简单例子
u012240777的博客
05-26 294
SELECT * FROM  empinf drop table empinf --创建一个员工信息表 create table empinf(    name             VARCHAR2(32) default SYS_GUID() not null,  --员工姓名    id               NUMBER(24)   not null,          
ORACLE重建索引总结
12-13
一、重建索引的前提 1、表上频繁发生update,delete操作； 2、表上发生了alter table ..move操作（move操作导致了rowid变化）。 二、重建索引的标准 1、索引重建是否有必要，一般看索引是否倾斜的严重，是否浪费了空间， 那应该如何才可以判断索引是否倾斜的严重，是否浪费了空间， 对索引进行结构分析（如下）： SQL>Analyze index index_name validate structure; 2、在执行步骤1的session中查询index_stats表，不要到别的session去查询。 SQL>select height,DEL_LF_ROWS/LF_ROWS from index_stats; 说明：当 查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合 ， 该索引考虑重建 。 举例： (t_gl_assistbalance 26 万多条信息 ) SQL> select count(*) from t_gl_assistbalance ; 输出结果： COUNT(*) ---------- 265788 SQL> Analyze index IX_GL_ASSTBAL_1 validate structure; Index analyzed SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats; 输出结果： HEIGHT DEL_LF_ROWS/LF_ROWS ---------- ------------------- 4 1 三、重建索引的方式 1、drop 原来的索引，然后再创建索引; 举例： 删除索引：drop index IX_PM_USERGROUP; 创建索引：create index IX_PM_USERGROUP on T_PM_USER (fgroupid); 说明：此方式耗时间，无法在24*7环境中实现，不建议使用。 2 、直接重建： 举例： alter index indexname rebuild; 或alter index indexname rebuild online; 说明：此方式比较快，可以在24*7环境中实现，建议使用此方式。 四、alter index rebuild 内部过程和注意点 alter index rebuild 和alter index rebuil online的区别 1、扫描方式不同 1.1、Rebuild以index fast full scan（or table full scan） 方式读取原索引中的数据来构建一个新的索引，有排序的操作; 1.2、rebuild online 执行表扫描获取数据，有排序的操作; 说明：Rebuild 方式 (index fast full scan or table full scan 取决于统计信息的cost) 举例1 SQL> explain plan for alter index IX_GL_ASSTBAL_1 rebuild; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 | | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | | | 2 | SORT CREATE INDEX | | 999K| 4882K| | | 3 | INDEX FAST FULL SCAN | IDX_POLICY_ID2 | 999K| 4882K| | --------------------------------------------------------------------- 举例2 SQL> explain plan for alter index idx_policy_id rebuild; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------- | 0 | ALTER INDEX STATEMENT | | 2072K| 9M| 461 | | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID | | | | | 2 | SORT CREATE INDEX | | 2072K| 9M| | | 3 | TABLE ACCESS FULL | TEST_INDEX | 2072K| 9M| 461 | 举例3 ( 注意和 举例1 比较 ) Rebuil online 方式 ： SQL> explain plan for alter index idx_policy_id2 rebuild online; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------------------| 0 | ALTER INDEX STATEMENT | | 999K| 4882K| 3219 | | 1 | INDEX BUILD NON UNIQUE| IDX_POLICY_ID2 | | | | | 2 | SORT CREATE INDEX | | 999K| 4882K| | | 3 | TABLE ACCESS FULL | TEST_INDEX2 | 999K| 4882K| 3219 | 2 、rebuild 会阻塞 dml 操作 ，rebuild online 不会阻塞 dml 操作 ； 3 、rebuild online 时系统会产生一个 SYS_JOURNAL_xxx 的 IOT 类型的系统临时日志表 ， 所有 rebuild online 时索引的变化都记录在这个表中 ， 当新的索引创建完成后 ， 把这个表的记录维护到新的索引中去 ， 然后 drop 掉旧的索引 ，rebuild online 就完成了。 注意点： 1、 执行rebuild操作时，需要检查表空间是否足够； 2、虽然说rebuild online操作允许dml操作，但是还是建议在业务不繁忙时间段进行； Rebuild操作会产生大量redo log ； 五、重建分区表上的分区索引 重建分区索引方法： Alter index indexname rebuild partition paritionname tablespace tablespacename; Alter index indexname rebuild subpartition partitioname tablespace tablespacename; Partition name 可以从user_ind_partitions查找 Tablepace 参数允许alter index操作更改索引的存储空间； 六、索引状态描述 在数据字典中查看索引状态，发现有三种： valid:当前索引有效 N/A :分区索引 有效 unusable:索引失效 七、术语 1、高基数：简单理解就是表中列的不同值多。 2、低基数：建单理解就是表中的列的不同值少。 3、以删除的叶节点数量：指得是数据行的delete操作从逻辑上删除的索引节点 的数量，要记住oracle在删除数据行后，将 “ 死 “ 节点保留在索引中，这样做可以加快sql删除操作的速度，因此oracle删除数据行后可以不必重新平衡索引。 4、索引高度：索引高度是指由于数据行的插入操作而产生的索引层数，当表中添加大量数据时，oracle将生成索引的新层次以适应加入的数据行，因此，oracle索引可能有4层，但是这只会出现在索引数中产生大量插入操作的区域。Oracle索引的三层结构可以支持数百万的项目，而具备4层或是更多层的需要重建。 5、每次索引访问的读取数：是指利用索引读取一数据行时所需要的逻辑I/O操作数，逻辑读取不必是物理读取，因为索引的许多内容已经保存在数据缓冲区，然而，任何数据大于10的索引都需要重建。 6、什么时候重建呢？ 察看 dba_indexes 中的 blevel 。这列是说明索引从根块到叶快的级别，或是深度。如果级别大于等于4。则需要重建， 如下 ：Select index_name,blevel from dba_indexes where blevel>=4. 另一个从重建中受益的指标显然是当该索引中的被删除项占总的项数的百分比。如果在20%以上时，也应当重建，如下 SQL>analyze index index_name validate structure SQL>select (del_lf_rows_len/lf_rows_len)*100 from index_stats where name= ’ index_name ’ 就能看到是否这个索引被删除的百分比。 7、什么样的重建方式更好？ （1）、建索引的办法： 1.1、删除并从头开始建立索引。 1.2 、 使用 alter index index_name rebuild 命令重建索引。 1.3 、 使用 alter index index_name coalesce 命令重建索引。 （2）、下面讨论一下这三种方法的优缺点： 2.1、删除并从头开始建索引：方法是最慢的，最耗时的。一般不建议。 2.2、Alter index index_name rebuild 快速重建索引的一种有效的办法，因为使用现有索引项来重建新索引，如果客户操作时有其他用户在对这个表操作，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题，alter index index_name rebuild online。 但是,由于新旧索引在建立时同时存在，因此，使用这种技巧则需要有额外的磁盘空间可临时使用，当索引建完后把老索引删除，如果没有成功，也不会影响原来的索引。利用这种办法可以用来将一个索引移到新的表空间。 Alter index index_name rebuild tablespace tablespace_name 。 这个命令的执行步骤如下： 首先，逐一读取现有索引，以获取索引的关键字。 其次，按新的结构填写临时数据段。 最后，一旦操作成功，删除原有索引树，降临时数据段重命名为新的索引。 需要注意的是alter index index_name rebuild 命令中必须使用tablespace字句，以保证重建工作是在现有索引相同的表空间进行。 2.3、alter index index_name coalesce 使用带有coalesce参数时重建期间不需要额外空间，它只是在重建索引时将处于同一个索引分支内的叶块拼合起来，这最大限度的减少了与查询过程中相关的潜在的加锁问题，但是，coalesce选项不能用来将一个索引转移到其他表空间。 八、其他 1、truncate 分区操作和truncate 普通表的区别？ 1.1、Truncate 分区操作会导致全局索引失效; truncate 普通表对索引没有影响； 1.2、Truncate 分区操作不会释放全局索引中的空间，而truncate 普通表会释放索引所占空间； 2、rename 表名操作对索引没有影响，因为rename操作只是更改了数据字典，表中数据行的rowid并没有发生变化 总结： 1、判断是否需要重建索引： SQL>analyze index index_name validate structure; SQL> select height,DEL_LF_ROWS/LF_ROWS from index_stats; （ 或 Select index_name,blevel from dba_indexes where blevel>=4 ）; 说明 ： 当查询出来的 height>=4 或者 DEL_LF_ROWS/LF_ROWS>0.2 的场合 ， 该索引考虑重建 ； 2 、重建索引方法 ： 方法一、 Alter index index_name rebuild tablespace tablespace_name; 优点：是快速重建索引的一种有效的办法，可以用来将一个索引移到新的表空间。 缺点：重建期间需要额外空间。 方法二、 alter index index_name coalesce; 优点：重建期间不需要额外空间。 缺点：coalesce选项不能用来将一个索引转移到其他表空间。
Oracle 如何删除主键新增主键
qq_40296545的博客
04-27 1711
Oracle 如何删除主键新增主键 ALTER TABLE 表名 drop CONSTRAINT 主键名; ALTER TABLE 表名 add CONSTRAINT 主键名 primary key (主键1,主键2,主键3); 在添加主键前一定要先删除主键约束，否则直接添加会不成功！ 如何修改默认值： alter table 表名 modify 列名 default 值; 如何添加字段 alter table 表名 add 字段VARCHAR2(1) default 'N'; ...
oracle删除主键约束的问题m
SnowShana的专栏
11-09 1140
oracle“删除”主键约束的方法有两个 1：alter table 表名 drop primary key; 这个是把主键从表中去除，而不是真正的删除主键 例子： 创建表：create table test_table_students （student_id number not null,student_name varchar(20) not null,student_te
oracle命名主键约束的原因
安善良民弱女子的博客
11-03 861
开始看 《精通Oracle Database 12c SQL & PL/SQL 编程（第三版）》 作者：Jason Price 著 卢涛 译oracle命名主键约束的原因salary_grade_id integer constraint salary_grade_pk primary key; 如果salary_grade_id中已经有1，在继续插入1这条数据时，就会报错，而错误信息中
Oracle中添加删除主键的方法（转）
zzu_shuainanhai的博客
06-13 5226
原文地址https://www.jb51.net/article/102176.htm
oracle索引的增删,ORACLE索引的管理
weixin_35487052的博客
04-03 86
前言：数据库运行了一段时间之后，由于频繁的进行了增删改，索引就会产生碎片，碎片多了自然而然的就会影响查询的效率。通过ORACLE提供的一些管理视图和工具就可以分析出数据库的哪些索引由于碎片太多需要进行重建；一、查找数据库比较占用空间的索引需要根据业务设定搜索的条件，索引大小大于特定值，索引的层数大于1的索引Select a.index_name,a.table_name,a.blevel,B.B...
oracle添加主键的四种方法:
cn7007的博客
11-02 633
oracle添加主键的四种方法: 列级，表级建立主键 drop table constraint_test; 1.create table constraint_test ( name_id number not nul...
Oracle 创建自增主键
记录工作开发中遇到的技术难点，方便自己查看，也希望可以帮助到他人...
03-19 5621
说明： Oracle没有这个auto_increment属性，所以它无法像MySQL般在表内定义自增主键。但是，Oracle里的序列，可间接实现自增主键的作用。 序列： 序列（Sequence），又叫序列生成器，用于提供一系列的数字，开发人员使用序列生成唯一键。每次访问序列，序列按照一定的规律增加或者减少。序列的定义存储在SYSTEM表空间中，序列不像表，它不会占用磁盘空间，占用内存。 用途： 其主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。 1、创.
oracle创建自增主键
zhouxzcsdn的博客
11-20 2661
1、创建序列 create sequence SEQ_XXX minvalue 1 nomaxvalue start with 1 increment by 1 nocycle nocache; 此时可直接使用 insert into TABLE_XXX values (SEQ_XXX.nextval,‘张三’); 2、创建触发器 CREATE OR REPLACE TRIGGER TG_XXX ...
Oracle 添加联合主键
08-14
在Oracle中，添加联合主键可以通过创建复合主键约束来实现。复合主键是由两个或多个列组成的主键，它们一起唯一标识表中的每一行。 要添加联合主键，首先需要确保要作为主键的列已经存在于表中。然后可以使用ALTER ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值