|Lesson 8||TABLE, DROP, or DISABLE CONSTRAINT commands |
|Objective||Describe the effects of dropping a table and disabling or removing a constraint. |
TABLE, DROP, or DISABLE CONSTRAINT Commands
The final lesson in this module looks at removing tables and constraints from an existing database.
The syntax of these commands is quite simple. Let's look at dropping a table first.
The syntax is as follows:
DROP TABLE tablename [CASCADE CONSTRAINTS]
When you specify
CASCADE CONSTRAINTS, any foreign key constraints that reference the table being dropped are also dropped. The effects of dropping a table are not as simple as the command syntax.
Take a look at this Slide Show to see what happens.
Drop Oracle Table
- Here is a diagram showing the CUSTOMER table and all the related objects in the database.
- As illustrated here, some of the objects are dropped along with the table while other objects are left in the database.
Whenever you prepare to drop a table, make sure that you know which related objects, such as indexes, views, and foreign keys, will be affected.
Dropping a constraint is done using the
ALTER TABLE command as follows:
ALTER TABLE tablename DROP CONSTRAINT constraint_name
For example, if the constraint for the primary key of the
PET_STORE table is named
PET_STORE_PK, then the command to drop the primary key constraint is:
ALTER TABLE PET_STORE
DROP CONSTRAINT PET_STORE_PK
You might drop a constraint if you want to replace it with a different constraint, or if the constraint is causing problems while adding data to
the database. For example, if you have a
CHECK constraint that has a list of values that is not complete, you might want to drop and recreate that constraint.
There are times, especially during development, when you want to be able to ignore a constraint without actually removing it. For example, let's
say that you have a parent table called
ORDERS and a child table called
ORDER_DETAILS. During the test phase, you want to delete and reload all the data in the
ORDERS table while leaving the
ORDER_DETAILS table intact. The foreign key on the
ORDER_DETAILS table prevents you from deleting a parent row that has children. The best way to handle this is to disable the
FOREIGN KEY constraint temporarily while you delete and reload the parent table. The syntax for this is:
ALTER TABLE tablename DISABLE CONSTRAINT
In the example described, the code might look like this:
ALTER TABLE ORDER_DETAILS DISABLE CONSTRAINT
Once the data has been restored, issue this command to reinstate the constraint:
ALTER TABLE ORDER_DETAILS ENABLE CONSTRAINT
The next lesson concludes this module.