首页 » ORACLE » alter table rename talblename、columnname、indexname、constraintsname

alter table rename talblename、columnname、indexname、constraintsname

重命名表名、字段名、索引名、约束名

SQL> CREATE TABLE test1 (
2    col1  NUMBER(10) NOT NULL,
3    col2  VARCHAR2(50) NOT NULL);

Table created.

SQL> ALTER TABLE test1 ADD (
2    CONSTRAINT test1_pk PRIMARY KEY (col1));

Table altered.

SQL> DESC test1
Name                 Null?    Type
——————– ——– ——————–
COL1                 NOT NULL NUMBER(10)
COL2                 NOT NULL VARCHAR2(50)

SQL> SELECT constraint_name
2  FROM   user_constraints
3  WHERE  table_name      = ‘TEST1’
4  AND    constraint_type = ‘P’;

CONSTRAINT_NAME
——————————
TEST1_PK

1 row selected.

SQL> SELECT index_name, column_name
2  FROM   user_ind_columns
3  WHERE  table_name = ‘TEST1’;

INDEX_NAME            COLUMN_NAME
——————–  ——————–
TEST1_PK              COL1

1 row selected.

SQL> — Rename the table, columns, primary key
SQL> — and supporting index.
SQL> ALTER TABLE test1 RENAME TO test;

Table altered.

SQL> ALTER TABLE test RENAME COLUMN col1 TO id;

Table altered.

SQL> ALTER TABLE test RENAME COLUMN col2 TO description;

Table altered.

SQL> ALTER TABLE test RENAME CONSTRAINT test1_pk TO test_pk;

Table altered.

SQL> ALTER INDEX test1_pk RENAME TO test_pk;

Index altered.

SQL> DESC test
Name                 Null?    Type
——————– ——– ——————–
ID                   NOT NULL NUMBER(10)
DESCRIPTION          NOT NULL VARCHAR2(50)

打赏

目前这篇文章有3条评论(Rss)评论关闭。

  1. Osvaldo Dohrman | #1
    2011-12-21 at 03:44

    Valuable info. Fortunate me I found your website by accident, and I’m shocked why this accident did not took place earlier! I bookmarked it.

  2. 外链医生 | #2
    2011-05-26 at 09:14

    博主好文,看能不能抢个沙发。小站为专业外链分析站点,欢迎回访。

    • Lakesha | #3
      2011-06-17 at 23:58

      Very true! Makes a chgane to see someone spell it out like that. 🙂