0

I have this oracle query which is running well in one environment and giving error in other environment. Both Environments have exact similar databases.

DECLARE
  v_temp NUMBER(1,0) := 0;
  BEGIN
  BEGIN
    SELECT 1 INTO v_temp FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_PDUCRC_MtrPrgAssc';        
    EXCEPTION WHEN NO_DATA_FOUND THEN v_temp := 0;
END;

IF v_temp = 0 THEN
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE METERCONFIGPDUCRC
                            ADD CONSTRAINT FK_PDUCRC_MtrPrgAssc
                            FOREIGN KEY (MeterProgramAssocID)
                            REFERENCES MeterProgramAssoc(MeterProgramAssocID)';
    END;
END IF;
END;

the error is-

such a referential constraint already exists in the table.

please help me regarding this issue.

Joe Taras
  • 15,166
  • 7
  • 42
  • 55
Vivek Mishra
  • 1,772
  • 1
  • 17
  • 37
  • 2
    Maybe the problem is that you are checking the existence of the constraint with upper and lower case? Try using upper(constraint_name) = upper('...') – Aleksej Apr 21 '16 at 12:55
  • 1
    exact similar databases my favorite type. :P Does FK_PDUCRC_MTRPRGASSOC already exist in the environment that' it is failing in? `Select * from all_Constraints where constraint_Name = 'FK_PDUCRC_MTRPRGASSC'` on the Environment it's failing... if record returns... it already exists and error is right. – xQbert Apr 21 '16 at 12:57
  • I can not make changes in query because it exists in our client build. Is there any parameter or setting in oracle db which can resolve this upper and lower case ambiguity? – Vivek Mishra Apr 21 '16 at 13:00
  • @xQbert if FK_PDUCRC_MTRPRGASSOC is already existing then according to the query if statement should not be executed. – Vivek Mishra Apr 21 '16 at 13:02
  • Ok, but as @Aleksej pointed out your query has 'FK_PDUCRC_MtrPrgAssc' (Note mixed case ending MtrPrgAssc) most System tables (if not all, and if not overridden with "" when creating) store such values as UPPER case. so the string compare will fail. – xQbert Apr 21 '16 at 13:04
  • @VIVEK : this is exactly the problem; FK_PDUCRC_MtrPrgAssc is different from FK_PDUCRC_MTRPRGASSOC, because of case; so, if you check the existence of 'FK_PDUCRC_MtrPrgAssc' while exists a constraint named FK_PDUCRC_MTRPRGASSOC, the check will fail. That's why you should check with UPPER, or use the exact (case SENSITIVE) constraint name in your check – Aleksej Apr 21 '16 at 13:05
  • @Aleksej ypu are right. what I want to know is there any setting in Oracle which can resolve this upper and lower case ambiguity. i.e. 'FK_PDUCRC_MtrPrgAssc'='FK_PDUCRC_MTRPRGASSOC' should return true. As I can not change the query. And this query is already working in another environment. – Vivek Mishra Apr 21 '16 at 13:09
  • 2
    http://stackoverflow.com/questions/5391069/case-insensitive-searching-in-oracle your can disable case sensitivity within a session. or adda unique index. – xQbert Apr 21 '16 at 13:14

3 Answers3

0

Assuming that you do NOT want to create constraints with the same name, but different case, you can try checking the existence of the constraint handling upper/lower case:

DECLARE
  v_temp NUMBER(1,0) := 0;
  BEGIN
    BEGIN
      SELECT 1 INTO v_temp FROM USER_CONSTRAINTS WHERE upper(CONSTRAINT_NAME) = upper('FK_PDUCRC_MtrPrgAssc');        
      EXCEPTION
        WHEN NO_DATA_FOUND
          THEN v_temp := 0;
    END;

  IF v_temp = 0 THEN
    BEGIN
        EXECUTE IMMEDIATE 'ALTER TABLE METERCONFIGPDUCRC
                            ADD CONSTRAINT FK_PDUCRC_MtrPrgAssc
                            FOREIGN KEY (MeterProgramAssocID)
                            REFERENCES MeterProgramAssoc(MeterProgramAssocID)';
    END;
  END IF;
END;
Aleksej
  • 22,443
  • 5
  • 33
  • 38
0

The error message doesn't sound to me like the constraint NAME is the problem. The error doesn't say a constraint by the same name already exists; it says a referential constraint already exists in this table.

Run DESCRIBE METERCONFIGPDUCRC and look to see if you already have a foreign key constraint on the column you are trying to add a FK constraint to. (DESCRIBE is a sqlplus command, it is understood by SQL Developer and Toad; use whatever equivalent tools you may have in your interface.)

0

It seems MeterProgramAssocID has already been made a foreign key referring to MeterProgramAssoc(MeterProgramAssocID) with a different constraint name. Check if such a constraint already exists.

SELECT A.CONSTRAINT_NAME FROM USER_CONSTRAINTS A, USER_CONS_COLUMNS B WHERE A.CONSTRAINT_TYPE = 'R' AND A.TABLE_NAME = 'METERCONFIGPDUCRC' AND A.OWNER = AND A.OWNER = B.OWNER AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME = B.TABLE_NAME AND B.COLUMN_NAME = UPPER('MeterProgramAssocID')

The above query should give you the existing constraint name. Hope this helps.

ArtBajji
  • 949
  • 6
  • 14