Re: How to disable foreign key constraints in a stored procedure?

Commens embedded.
laredotornado_at_zipmail.com wrote:
> Hi,
>
> Is there another way to do what I'm trying to do in Oracle 9i for
> Solaris? In a stored procedure, I want to disable some foreign key
> constraints, perform some actions, and then re-enable them.

Why? The constraints are in place for a good reason.

> However,
> when trying to compile the package, I get the errors. The code is
> below that.
>
> LINE/COL ERROR
> --------
> -----------------------------------------------------------------
> 57/2 PLS-00103: Encountered the symbol "ALTER" when expecting one
> of
> the following:
> begin case declare exit for goto if loop mod null pragma
> raise return select update while with <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> <<
> close current delete fetch lock insert open rollback
> savepoint set sql execute commit forall merge
> <a single-quoted SQL string> pipe
> The symbol "lock was inserted before "ALTER" to continue.
>
> 57/46 PLS-00103: Encountered the symbol "CONSTRAINT" when expecting
> one
>
>
> PROCEDURE edit_project_name(
> p_old_project_name IN VARCHAR2,
> p_new_project_name IN VARCHAR2
> )
> AS
> BEGIN
> ALTER TABLE SUPPORT.BUGTRACKER_DATA disable CONSTRAINT
> BD_PROJECT_FK; -- line 57
> ALTER TABLE SUPPORT.BUGTRACKER_ASSIGNEES disable CONSTRAINT
> BC_ASSIGNED_TO_FK;
> ALTER TABLE SUPPORT.BUGTRACKER_MY_PROJECTS disable CONSTRAINT
> FK_BUGTRACKER_MY_PJTS;
> UPDATE SUPPORT.BUGTRACKER_DATA SET PROJECT = p_new_project_name
> WHERE PROJECT = p_old_project_name;
> UPDATE SUPPORT.BUGTRACKER_ASSIGNEES SET PROJECT =
> p_new_project_name WHERE PROJECT = p_old_project_name;
> UPDATE SUPPORT.BUGTRACKER_PROJECTS SET PROJECT =
> p_new_project_name WHERE PROJECT = p_old_project_name;
> UPDATE SUPPORT.BUGTRACKER_MY_PROJECTS SET PROJECT =
> p_new_project_name WHERE PROJECT = p_old_project_name;
> ALTER TABLE SUPPORT.BUGTRACKER_DATA ENABLE CONSTRAINT
> BD_PROJECT_FK;
> ALTER TABLE SUPPORT.BUGTRACKER_ASSIGNEES ENABLE CONSTRAINT
> BC_ASSIGNED_TO_FK;
> ALTER TABLE SUPPORT.BUGTRACKER_MY_PROJECTS ENABLE CONSTRAINT
> FK_BUGTRACKER_MY_PJTS;
> COMMIT;
> EXCEPTION
> WHEN OTHERS THEN
> ROLLBACK;
> END;
>
> Your suggestions are apprecaited, - Dave

My suggestion would be to re-think your 'reasoning' for disabling the foreign keys. I can think of no good reason to do so. It is highly likely you'll end up with invalid foreign key constraints and ruin your referential integrity.

That being said DDL is not allowed, directly, in PL/SQL procedures. You've been given a possible 'solution', but, to me, this situation you're creating isn't a solution, it's a problem waiting to blow up in your face, therefore I'll not repeat the workaround here.

David Fitzjarrell Received on Fri Mar 17 2006 - 10:24:24 CST