CODEWITHSUNDEEP
sqloracle-databaseprimary-keycreate-table
ZZZ
ZZZ

Reputation: 955

How to delete primary key constraint after table is droppped in Oracle

I have a table:

CREATE TABLE SHIP_CABINS
(
    SHIP_CABIN_ID NUMBER,
    SHIP_ID NUMBER,
    GUESTS NUMBER(3),
    SQ_FT NUMBER(6),
    CONSTRAINT SHIP_CABIN_ID_PK PRIMARY KEY (SHIP_CABIN_ID)
);

I then dropped the table like this:

DROP TABLE SHIP_CABINS;

Because I didn't use CASCADE CONSTRAINTS, the primary key constraint SHIP_CABIN_ID_PK still exists after the table is dropped.

I don't know how to remove the primary key after the table no longer exists. Please help.

Upvotes: 0

Views: 637

Answers (1)

GMB
GMB

Reputation: 222482

There seems to be a misconception here. Dropping a table does removes its primary key constraint.

cascade constraints is a different beast, that is meant to automatically remove foreign key constraints that reference the table being dropped. If there are such constraint, the database prevents you from dropping the table, unless you specify that option.

Consider the following demo:

-- create the table
create table ship_cabins (
    ship_cabin_id number,
    ship_id number,
    guests number(3),
    sq_ft number(6),
    constraint ship_cabin_id_pk primary key (ship_cabin_id)
);

-- check the table constraints
select constraint_name, constraint_type from user_constraints where table_name = 'SHIP_CABINS';

CONSTRAINT_NAME  | CONSTRAINT_TYPE
:--------------- | :--------------
SHIP_CABIN_ID_PK | P              

-- drop the table
drop table ship_cabins;

-- the PK constraint is gone
select constraint_name, constraint_type from user_constraints where table_name = 'SHIP_CABINS';

CONSTRAINT_NAME | CONSTRAINT_TYPE
:-------------- | :--------------

Upvotes: 1

Related Questions