| change the name of column [message #9990] |
Thu, 18 December 2003 23:38  |
RAMMY
Messages: 2
Registered: December 2003
|
Junior Member |
|
|
i created the tabels in that each table have primary
key and aswell as forignkey. in first table their is a
column name depatment. so i want to change the name
by dept.what can i do
|
|
|
|
|
|
| Re: change the name of column [message #9993 is a reply to message #9990] |
Fri, 19 December 2003 01:54  |
 |
Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
In Oracle 9i, you can rename a column of the primary key and the primary and foreign key constraints are still maintained. Please see the example below.
scott@ORA92> CREATE TABLE first_table AS SELECT deptno department, dname, loc FROM dept
2 /
Table created.
scott@ORA92> CREATE TABLE second_table AS SELECT * FROM emp
2 /
Table created.
scott@ORA92> ALTER TABLE first_table ADD CONSTRAINT first_table_deptno_pk PRIMARY KEY (department)
2 /
Table altered.
scott@ORA92> ALTER TABLE second_table ADD CONSTRAINT second_table_deptno_fk FOREIGN KEY (deptno)
2 REFERENCES first_table (department)
3 /
Table altered.
scott@ORA92> <b>ALTER TABLE first_table RENAME COLUMN department TO dept
2 /</b>
Table altered.
scott@ORA92> INSERT INTO first_table (dept) VALUES (99)
2 /
1 row created.
scott@ORA92> INSERT INTO second_table (deptno, empno) VALUES (99, 999)
2 /
1 row created.
scott@ORA92> INSERT INTO first_table (dept) VALUES (99)
2 /
INSERT INTO first_table (dept) VALUES (99)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.FIRST_TABLE_DEPTNO_PK) violated
scott@ORA92> INSERT INTO second_table (deptno, empno) VALUES (88, 300)
2 /
INSERT INTO second_table (deptno, empno) VALUES (88, 300)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SECOND_TABLE_DEPTNO_FK) violated - parent key not found
scott@ORA92>
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
