Renaming Columns And Constraints

本文介绍如何使用Oracle9i对表、列、主键约束及支持该约束的索引进行重命名。通过一系列SQL命令展示了创建表、添加主键约束、查看约束和索引信息,并成功对这些元素进行了重命名。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

n addition to renaming tables and indexes Oracle9i Release 2 allows the renaming of columns and constraints on tables. In this example once the the TEST1 table is created it is renamed along with it's columns, primary key constraint and the index that supports the primary key:

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)

SQL> SELECT constraint_name
2 FROM user_constraints
3 WHERE table_name = 'TEST'
4 AND constraint_type = 'P';

CONSTRAINT_NAME
--------------------
TEST_PK

1 row selected.

SQL> SELECT index_name, column_name
2 FROM user_ind_columns
3 WHERE table_name = 'TEST';

INDEX_NAME COLUMN_NAME
-------------------- --------------------
TEST_PK ID

1 row selected.
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值