Difference between revisions of "Oracle:DDL Drop"
From mi-linux
Jump to navigationJump to search (Created page with "Main Page >> Oracle and SQL >> Workbook >> DDL >> Drop == Removing a Table == A table can be deleted b...") |
|||
| (5 intermediate revisions by the same user not shown) | |||
| Line 3: | Line 3: | ||
== Removing a Table == | == Removing a Table == | ||
| − | A table can be | + | A table can be removed by using the following command: |
| − | + | <code> | |
| + | DROP TABLE tablename; | ||
| + | </code> | ||
where tablename is the name of the table to be deleted. | where tablename is the name of the table to be deleted. | ||
| − | Only use this if you want to get rid of a table. | + | '''Only use this if you want to get rid of a table'''. |
| + | |||
| + | For example, remove the identity_test table: | ||
| + | |||
| + | DROP TABLE identity_test; | ||
| − | Do note, that if you have a foreign key linked to the table being dropped, you will not be able to remove it | + | |
| + | == Referential Integrity Issues == | ||
| + | |||
| + | Do note, that if you have a foreign key linked to the table being dropped, you will not be able to remove it: | ||
DROP TABLE DEPT; | DROP TABLE DEPT; | ||
| Line 17: | Line 26: | ||
Should return an error message: | Should return an error message: | ||
| − | < | + | <pre style="color: blue"> |
ERROR at line 1: | ERROR at line 1: | ||
ORA-02449: unique/primary keys in table referenced by foreign keys | ORA-02449: unique/primary keys in table referenced by foreign keys | ||
| − | </ | + | </pre> |
In this case the EMP table references the DEPT table, so you would have to remove it first. If you have carried out the previous [[Oracle:DDL_Create|CREATE TABLE]] section, EMP in turn is now referenced by the EMPPROP table, so it would have to be removed before the EMP and so on.... | In this case the EMP table references the DEPT table, so you would have to remove it first. If you have carried out the previous [[Oracle:DDL_Create|CREATE TABLE]] section, EMP in turn is now referenced by the EMPPROP table, so it would have to be removed before the EMP and so on.... | ||
| + | |||
== Next Step == | == Next Step == | ||
[[Oracle:DDL_Alter|Altering tables]]. | [[Oracle:DDL_Alter|Altering tables]]. | ||
Latest revision as of 19:20, 1 March 2016
Main Page >> Oracle and SQL >> Workbook >> DDL >> Drop
Removing a Table
A table can be removed by using the following command:
DROP TABLE tablename;
where tablename is the name of the table to be deleted.
Only use this if you want to get rid of a table.
For example, remove the identity_test table:
DROP TABLE identity_test;
Referential Integrity Issues
Do note, that if you have a foreign key linked to the table being dropped, you will not be able to remove it:
DROP TABLE DEPT;
Should return an error message:
ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys
In this case the EMP table references the DEPT table, so you would have to remove it first. If you have carried out the previous CREATE TABLE section, EMP in turn is now referenced by the EMPPROP table, so it would have to be removed before the EMP and so on....
