Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2010
    Posts
    1

    Unanswered: oracle problem with foregn keys

    hi
    i have 7 tables and i need to put the same foreign key in 3 tables. I am trying but I cant do it. I dont know too much about oracle.
    i got this error
    ORA-02264: name already used by an existing constraint

    Anyone help me please

    cheers

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Anyone help me please
    Help you how?
    You gave us NOTHING to work with here!

    >i have 7 tables
    We don't have DDL to create them (we don't know table names, column names, or column datatypes)

    >i need to put the same foreign key in 3 tables
    We don't know which 3 tables need this.
    We don't know which columns are the Foreign Keys.

    >ORA-02264: name already used by an existing constraint
    We don't know EXACTLY what you did to get this error
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I think you have copied the constraint definition and simply changed the table name. The constraint name must be unique in the database. Most people use a standard naming convention such as tablename_{first letters of the columns that make up the constraint)_{fk | pk} where fk - indicates a foreign key and pk a primary key.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by Ronan
    The constraint name must be unique in the database.
    Not exactly; unique within a schema - yes, but not in the database.
    Code:
    SQL> connect scott/tiger
    Connected.
    
    SQL> create table test_1 (id number constraint pk_1 primary key);
    
    Table created.
    
    SQL> connect mike/lion
    Connected.
    
    SQL> create table test_1 (id number constraint pk_1 primary key);
    
    Table created.
    
    SQL> create table test_2 (id number constraint pk_1 primary key);
    create table test_2 (id number constraint pk_1 primary key)
                                              *
    ERROR at line 1:
    ORA-02264: name already used by an existing constraint
    
    
    SQL>

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Indeed - thanks for the clarification!!
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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