Drop column with FK via PSD edit table causes errors

[Bernhard S]

To demonstrate the behaviour I use the traditional EMP and DEPT table as created via $ORACLE_HOME/rdbms/admin/utlsampl.sql:

CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
  DNAME VARCHAR2(14) ,
  LOC VARCHAR2(13) ) ;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);

Then I open the PSD "Edit" right-click option on table EMP and remove the column DEPTNO there. Note that this column is also FK-referenced to the PK of DEPT column DEPTNO.
The generated code by PSD for this column removal is:

-- Drop columns
alter table EMP drop column deptno;
-- Create/Recreate primary, unique and foreign key constraints
alter table EMP
  drop constraint FK_DEPTNO;
alter table EMP
  add constraint FK_DEPTNO foreign key ()
  references DEPT (DEPTNO);

Actually the first alter table already does the whole job. I haven't seen any FK yet that would still be existing even though the column it's based on is gone - the second alter table is pointless therefore.
The best is however the final statement trying to "recreate" the FK using the now non-existing column.

You consequently get the following errors:
ORA-02443: Cannot drop constraint - nonexistent constraint
ORA-00936: missing expression

 

[Marco Kalter]

We'll fix it.