Database Reference
In-Depth Information
The term “UK_” implies “unique key.” Technically, this constraint is
actually better termed as an alternate key rather than a unique key, because
the table already has a unique identifier in the form of a primary key created
on the ARTIST_ID column. So we could change the name of the con-
straint from UK_ARTISTS to AK_ARTISTS using the ALTER TABLE
command shown as follows:
ALTER TABLE ARTISTS RENAME CONSTRAINT UK_ARTISTS
TO AK_ARTISTS;
20.3.5
Dropping Constraints
As we can see in Figure 20.8, constraints can be dropped with the ALTER
TABLE command.
Currently, we should still have the SONGS and ARTISTS tables in the
MUSIC schema. Let's go ahead and drop all of the constraints on those two
tables. We will start with the unique (alternate) keys on the
SONGS.TITLE and the ARTISTS.NAME columns. Note the two differ-
ent methods of dropping the unique constraints. The first does not use the
constraint name. This is useful when the constraint was created without a
specific name. The actual constraint name was system-generated and is
something like SYS_C004463 and can be found by querying the
USER_CONSTRAINTS data dictionary view (more on this later).
ALTER TABLE SONGS DROP UNIQUE(TITLE);
ALTER TABLE ARTISTS DROP CONSTRAINT AK_ARTISTS;
Now let's drop Referential Integrity constraints between the ARTISTS
and SONGS tables. We have to drop the foreign key on the SONGS table
before we drop the primary key on the ARTISTS table. If we try to drop
the primary key on the ARTISTS table first, we will get an error because the
foreign key on the SONGS table depends on the primary key on the ART-
ISTS table. Again, notice that the first and third commands use only the
type of constraint, not the name.
ALTER TABLE SONGS DROP PRIMARY KEY;
ALTER TABLE SONGS DROP CONSTRAINT FK_ARTISTS;
ALTER TABLE ARTISTS DROP PRIMARY KEY;
