• 删除主键时报错ORA-00955


    一。利用已有索引创建主键
    1.建表
    GAO@PROD> create table abcd(id number(10),name1 varchar2(20));

    Table created.

    2.插入数据
    GAO@PROD> insert into abcd values(1,'genwang');

    1 row created.

    GAO@PROD> commit;

    Commit complete.

    3.给id字段创建索引
    GAO@PROD> create index pk_abcd on abcd(id);

    Index created.

    4.利用已有索引创建主键
    GAO@PROD> alter table abcd add constraint pk_abcd primary key(id);

    Table altered.


    5.查看表的索引和约束
    GAO@PROD> select index_name from user_indexes where table_name=upper('abcd');

    INDEX_NAME
    ------------------------------------------------------------------------------------------
    PK_ABCD


    GAO@PROD> select constraint_name from user_constraints where table_name=upper('abcd');

    CONSTRAINT_NAME
    ------------------------------------------------------------------------------------------
    PK_ABCD


    6.删除主键
    GAO@PROD> alter table abcd drop primary key;

    Table altered.

    7.查看索引和约束是否已经被删除,发现索引还在,约束没了
    GAO@PROD> select index_name from user_indexes where table_name=upper('abcd');

    INDEX_NAME
    ------------------------------------------------------------------------------------------
    PK_ABCD


    GAO@PROD> select constraint_name from user_constraints where table_name=upper('abcd');

    no rows selected

    8.用别的字段重建主键,发现报错 ORA-00955
    alter table abcd add constraint pk_abcd primary key(id,name1);

    GAO@PROD> alter table abcd add constraint pk_abcd primary key(id,name1);
    alter table abcd add constraint pk_abcd primary key(id,name1)
    *
    ERROR at line 1:
    ORA-00955: name is already used by an existing object


    解决办法:
    1)删除原来的索引,然后再创建新的主键
    GAO@PROD> drop index pk_abcd;

    Index dropped.

    2)GAO@PROD> alter table abcd add constraint pk_abcd primary key(id,name1);

    Table altered.


    9.查看此时abcd表的索引和约束(可以看到如果该表主键列没有索引,这时创建主键时会自动在主键列创建索引)
    GAO@PROD> select index_name,uniqueness from user_indexes where table_name=upper('abcd');

    INDEX_NAME UNIQUENESS
    ------------------------------------------------------------------------------------------ ---------------------------
    PK_ABCD UNIQUE


    GAO@PROD> select constraint_name from user_constraints where table_name=upper('abcd');

    CONSTRAINT_NAME
    ------------------------------------------------------------------------------------------
    PK_ABCD

    9.删除主键
    GAO@PROD> alter table abcd drop primary key;

    Table altered.

    10.查看索引和约束状态(发现索引和约束都不复存在)
    GAO@PROD> select index_name from user_indexes where table_name=upper('abcd');

    no rows selected

    GAO@PROD> select constraint_name from user_constraints where table_name=upper('abcd');

    no rows selected

    11.此时添加新的主键,不再报错
    alter table abcd add constraint pk_abcd primary key(id,name1);


    结论:如果主键是基于原来索引创建的,那么alter table XXX drop primary key只能删除约束,无法删除原来索引。如果此时创建主键时,主键列与索引列不同,那么就会报错ORA-00955: name is already used by an existing object。
    如果主键创建时,主键列没有索引,那么创建主键时会自动在主键列创建唯一索引,这时alter table XXX drop primary key就会把约束和索引一起删除。重新创建主键时就不会报错。

  • 相关阅读:
    ACM的探索之Keen On Evrything But Triangle(我觉得可以很接近啦!!)
    ACM的探索之Just Skip The Problem
    ACM的探索之Everything Is Generated In Equal Probability(这真的是很有趣的话语丫!)
    心情散记
    识别有效的IP地址和掩码并进行分类统计
    网络流复习计划
    CF1076D Edge Deletion
    bzoj4563 HAOI2016放旗子
    BZOJ2152聪聪可可
    BZOJ3224普通平衡树
  • 原文地址:https://www.cnblogs.com/gw666/p/14916970.html
Copyright © 2020-2023  润新知