Home > Back-end >  implementing automatic deletion of a (related) row of record in PartyEvent table, when a row from th
implementing automatic deletion of a (related) row of record in PartyEvent table, when a row from th

Time:11-02

CREATE TABLE PARTY_EVENT
(
    passport_number VARCHAR(20)  NOT NULL,
    event_id        DECIMAL(10)  NOT NULL,
    event_date      DATE         NOT NULL,
    event_time      CHAR(5)      NOT NULL,
    event_venue     VARCHAR(50)  NOT NULL,
    party_size      CHAR(3)      NOT NULL,
    cuisine_package VARCHAR(100) NOT NULL,

    CONSTRAINT PARTY_EVENT_PK PRIMARY KEY(event_id),
    CONSTRAINT PARTY_EVENT_FK  
        FOREIGN KEY(passport_number) REFERENCES CLIENT(passport_number)
             ON DELETE CASCADE,
    CONSTRAINT PARTY_EVENT_FK1 
        FOREIGN KEY(passport_number) REFERENCES CHEF  (passport_number)
             ON DELETE CASCADE,
    CONSTRAINT PARTY_EVENT_CK2 UNIQUE(event_date, event_time, event_venue)
);

ALTER TABLE PARTY_EVENT 
DROP FK_passport_number;

Error:

ALTER TABLE PARTY_EVENT
DROP FK_passport_number

Error Code: 1091. Can't DROP 'FK_passport_number'; check that column/key exists

CodePudding user response:

Assumptions as you've not specified the exact question:

  1. I expect you'd like to drop a constraint as your code drops the constraint which should be:
ALTER TABLE PARTY_EVENT 
DROP CONSTRAINT FK_Passport_Number

If you'd want to drop the column passport:

ALTER TABLE PARTY_EVENT
DROP COLUMN passport_number;

CodePudding user response:

In your create table statement, there is no fk_passport_number constraint, I'm assuming you meant party_event_fk and party_event_fk1.

You first need to drop the foreign key constraint:

MySQL:

ALTER TABLE party_event DROP FOREIGN KEY party_event_fk;
ALTER TABLE party_event DROP FOREIGN KEY party_event_fk1;

SQL Server / Oracle / MS Access:

ALTER TABLE party_event DROP CONSTRAINT party_event_fk;
ALTER TABLE party_event DROP CONSTRAINT party_event_fk1;

Then you can drop the actual column:

ALTER TABLE party_event DROP COLUMN passport_number;

The problem was that you were trying to drop the constraint like it was a column.

  • Related