Results 1 to 3 of 3

Thread: Ora-02264:

  1. #1
    Join Date
    Nov 2011
    Posts
    3

    Unanswered: Ora-02264:

    Hi everyone,
    I am hoping someone can help me out,
    I am new to databases ans am currently working on makin a very very basic database in Oracle 10g express.
    I am getting the error
    'ORA-02264: name already used by an existing constraint'

    I understand that this means my constraint is not unique however, as far as I am aware it is unique!!
    Would I get this error if I am trying to make a foreign key constrain on a field, that already has a foreign key constraint in another table i.e.
    Would:
    1. CONSTRAINT CAREPROVIDER_FOREIGN_KEY FOREIGN KEY (SP_CODE) REFERENCES SPECIALITY(SP_CODE)
    in one table

    cause an error with

    2. CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (SP_CODE) REFERENCES SPECIALITY(SP_CODE),

    in another table...???

    I have attached the DDL and would be extremly greatful if someone could help me!!! It is very basic with 6 small tables.

    I am getting the error on tables EPISODE and DEPTHEADS. All other tables are OK.

    Thank You Faustian_1
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    to save people the time and effort of downloading specialized software just to open a strange wordprocessing file, i've taken the liberty of copying your DDL to plain text
    Code:
    DROP TABLE PERSON;
    DROP TABLE BILLING;
    DROP TABLE LOCATION;
    DROP TABLE ROOM;
    DROP TABLE SPECIALITY;
    DROP TABLE CAREPROVIDER;
    DROP TABLE EPISODE;
    DROP TABLE DEPTHEADS;
    
    Create table PERSON (
    PA_NUM		number (8) PRIMARY KEY,
    PA_LNAME		Varchar2 (20) NOT NULL,
    PA_FNAME 		Varchar2 (20) NOT NULL,
    PA_ADDRESSL1 		Varchar2 (40) NOT NULL,
    PA_ADDRESSCITY	 Varchar2 (20) Not NULL,
    PA_ZIP			char (5) NOT NULL,
    PA_AREACODE		number (6),
    PA_TELEPHONE		Number (10),
    PA_DOB		Date NOT NULL,
    PA_TITLE		Varchar2 (4) NOT NULL
    );
    
    Create table BILLING (
    BL_CODE		Number (8) PRIMARY KEY,
    BL_STATUS		Varchar2 (10) NOT NULL,
    BL_CONTACTDATE	Date,
    BL_CLOSEDATE		Date,
    BL_TOTAL		Decimal (7,2) NOT NULL,
    BL_AMOUNTPAID	Decimal (7,2),
    BL_OUTSTANDING	Decimal (7,2),
    BL_STARTDATE		Date
    );
    
    Create table LOCATION
    (
    LOC_CODE		Number (5) Primary Key,
    LOC_DESCRIPTION	Varchar2 (15) NOT NULL,
    LOC_UNITPRICE		Decimal (7,2) NOT NULL,
    LOC_TELEPHONE	Number (8) NOT NULL
    );
    
    Create table ROOM
    (
    RM_CODE	Number(4)PRIMARY KEY,
    RM_DESCRITPION	Varchar2 (6) NOT NULL,
    RM_TYPE		Varchar2 (10) NOT NULL,
    LOC_CODE		Number (5) NOT NULL,
    CONSTRAINT ROOM_FOREIGN_KEY FOREIGN KEY (LOC_CODE) REFERENCES LOCATION (LOC_CODE)
    );
    
    Create table SPECIALITY
    (
    SP_CODE		Number(6) PRIMARY KEY,
    SP_DESCRIPTION	Varchar2 (20) NOT NULL
    );
    
    Create table CAREPROVIDER
    (
    CP_GMCNUM		Number (8) PRIMARY KEY, 
    CP_TITLE		Varchar2 (6) NOT NULL,
    CP_FNAME		Varchar2 (20) NOT NULL,
    CP_LNAME		Varchar2 (20) NOT NULL,
    CP_BLEEP_NUM	Number (4) NOT NULL,
    CP_ADDRESSL1		Varchar2 (255) NOT NULL,
    CP_ZIP			Number (5) NOT NULL,
    CP_DATEOFEMP	DATE NOT NULL,
    CP_DATETOEMP	DATE,
    SP_CODE		Number NOT NULL,
    CONSTRAINT CAREPROVIDER_FOREIGN_KEY FOREIGN KEY (SP_CODE) REFERENCES SPECIALITY(SP_CODE)
    );
    
    Create table EPSIODE (
    EP_NUM		Number (8)  PRIMARY KEY,
    PA_NUM		Number (8) NOT NULL,
    RM_CODE		NUMBER (4) NOT NULL,
    SP_CODE		NUMBER (6) NOT NULL,
    CP_GMCNUM		NUMBER (8) NOT NULL,
    EP_STATUS		Varchar2 (9) NOT NULL
    EP_TYPE		Varchar2 (9) NOT NULL,
    BL_CODE		Number (8) NOT NULL,
    EP_ADMITDATE		DATE NOT NULL,
    EP_DISCHARGEDATE	DATE,
    EP_DIAGNOSIS		Varchar2 (255),
    CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (PA_NUM) REFERENCES PERSON(PA_NUM),
    CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (RM_CODE) REFERENCES ROOM(RM_CODE),
    CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (SP_CODE) REFERENCES SPECIALITY(SP_CODE),
    CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (CP_GMCNUM) REFERENCES CAREPROVIDER(CP_GMCNUM),
    CONSTRAINT EPSIODE_FOREIGN_KEY FOREIGN KEY (BL_CODE) REFERENCES BILLING(BL_CODE)
    );
    
    CREATE TABLE DEPTHEADS (
    DE_NUMBER number(5) PRIMARY KEY,
    CP_GMCNUM		Number (8) NOT NULL,
    SP_CODE		Number (6) NOT NULL, 
    CONSTRAINT DEPTHEADS_FOREIGN_KEY FOREIGN KEY (CP_GMCNUM) REFERENCES CAREPROVIDER(CP_GMCNUM),
    CONSTRAINT DEPTHEADS_FOREIGN_KEY FOREIGN KEY (SP_CODE) REFERENCES SPECIALITY(SP_CODE)
    );
    i would like you to pay particular attention to the parts i've highlighted in red

    does the error message "name already used by an existing constraint" make more sense now?

    each constraint has to have a unique name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    I understand that this means my constraint is not unique however, as far as I am aware it is unique!!
    Whatever your definition of uniqueness exactly is, Oracle defines "uniqeness" for constraints the following way: different constraints in a schema must have different names !

    In the table "EPISODE" alone there are 5 foreign key constraints you try to assign the same name !
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •