ORA-02264: name already used by an existing constraint how to get this solved?-CodePudding

CREATE TABLE report(
    report_id NUMBER(5),
    description VARCHAR2(200) NOT NULL,
    status VARCHAR2(200) NOT NULL,
    bicycle_id NUMBER(5),
    cust_id NUMBER(5),
    staff_id NUMBER(5),
CONSTRAINT ad_reportid_pk PRIMARY KEY (report_id),
CONSTRAINT ad_bicycle_fk FOREIGN KEY (bicycle_id) REFERENCES bicycle(bicycle_id),
CONSTRAINT ad_custid_fk FOREIGN KEY (cust_id) REFERENCES customer(cust_id),
CONSTRAINT ad_staffid_fk FOREIGN KEY (staff_id) REFERENCES staff(staff_id)
);

Above is the SQL script when I'm creating a table and it thrown me error with ORA-02264. I am neither drop the table nor creating that table. Tried to run this script

SELECT * FROM user_constraints WHERE CONSTRAINT_NAME = 'ad_bicycleid_fk'

and it showed no data.

CodePudding user response：

All object names are stored in uppercase unless you mention it in double quotes while creating an object. Try this:

SELECT * FROM user_constraints 
   WHERE upper(CONSTRAINT_NAME) = upper('ad_bicycleid_fk');

CodePudding user response：

Here's an example which shows one option you might do that.

First, "dummy" tables which are being referenced by columns in the report table (why do I need them? Because create table report would fail otherwise):

SQL> create table bicycle
  2    (bicycle_id  number(5) constraint pk_bic primary key);

Table created.

SQL> create table customer
  2    (cust_id     number(5) constraint pk_cus primary key);

Table created.

SQL> create table staff
  2    (staff_id    number(5) constraint pk_sta primary key);

Table created.

The report table:

SQL> create table report
  2    (report_id   number(5),
  3     description varchar2(200) not null,
  4     status      varchar2(200) not null,
  5     bicycle_id  number(5),
  6     cust_id     number(5),
  7     staff_id    number(5),
  8     --
  9     constraint pk_rep     primary key (report_id),
 10     constraint fk_rep_bic foreign key (bicycle_id)
 11                references bicycle (bicycle_id),
 12     constraint fk_rep_cus foreign key (cust_id)
 13                references customer (cust_id),
 14     constraint fk_rep_sta foreign key (staff_id)
 15                references staff (staff_id)
 16    );

Table created.

SQL>

Everything is OK.

Note the way I named constraints, especially foreign keys whose names show - at least approximately - which table references which another table, e.g. FK_REP_BIC - it is a Foreign Key from table REPort and it references the BICycle table. I'm not saying that you must do it that way, but - Google a little bit, read about good practices and find the one you prefer the most.

As of the constraint you couldn't find:

SQL> select owner, constraint_type, table_name
  2  from user_constraints
  3  where constraint_name = 'FK_REP_BIC';

OWNER           C TABLE_NAME
--------------- - ---------------
SCOTT           R REPORT

SQL>