If it doesn't create orphan's can't we drop a primary key?
My book is saying no. Steve O'Hearn 1z0-047 text page 432.
For example, consider this code:
CREATE TABLE CRUISE_ORDERS
(CRUISE_ORDER_ID NUMBER,
ORDER_DATE DATE,
CONSTRAINT PK_CO PRIMARY KEY (CRUISE_ORDER_ID, ORDER_DATE));
CREATE TABLE ORDER_RETURNS
(ORDER_RETURN_ID NUMBER,
CRUISE_ORDER_ID NUMBER,
CRUISE_ORDER_DATE DATE,
CONSTRAINT PK_OR PRIMARY KEY (ORDER_RETURN_ID),
CONSTRAINT FK_OR_CO FOREIGN KEY
(CRUISE_ORDER_ID, CRUISE_ORDER_DATE)
REFERENCES CRUISE_ORDERS (CRUISE_ORDER_ID, ORDER_DATE));
These SQL statements will create two tables with a FOREIGN KEY relationship
connecting them together—see Figure 11-1 for the data model representing the
relationship.
In the CRUISE_ORDERS table that we just created, we cannot drop the CRUISE_
ORDER_ID column, or the ORDER_DATE column, for two different reasons:
They form part of the PRIMARY KEY constraint, and constrained columns
cannot be dropped unless the constraint is first dropped;
They form part of the referred key in the FOREIGN KEY of another table.
They cannot be dropped as long as a FOREIGN KEY constraint refers to them.
Similarly, we cannot drop the column in the ORDER_RETURNS table that
is subject to the PRIMARY KEY constraint (ORDER_RETURN_ID), nor the..
You should easily be able to drop the order_return_id column. I did it myself. I only caught this after coming back to the chapter for a reread and it didn't make sense to me. Maybe because it's referring to PL/SQL or an older version? (book is 5 years old).
In any case I can't see why a primary key without dependent columns couldn't be dropped.
Like I said I tested it, and it can be dropped.