Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2011
    Posts
    11

    Unanswered: Trouble assigning foreign key

    I have this table product and it is linked with three tables. So I tried to assign foreign key product_no to three different tables which don't work. I successfully assigned foreign key to one of the table among three.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    "don't work" is not a valid Oracle error message.

    show us the CREATE TABLE for the tables involved
    show us the statement you tried to execute
    show us the exact error message you get.

    and make sure your SQL code is formatted using [code] tags to make it readable.

    For details on the tags see the help:
    http://www.dbforums.com/misc.php?do=bbcode#code

  3. #3
    Join Date
    Dec 2011
    Posts
    11

    Here's the error:::

    Product table:
    CREATE TABLE product (
    model_no NUMBER NOT NULL,
    manufacturer_no NUMBER,
    product_desc VARCHAR2(12),
    product_type VARCHAR(10),
    unit_price NUMBER,
    CONSTRAINT product_pk PRIMARY KEY(model_no)
    );
    product table is created successfully.
    CREATE TABLE computer (
    model_no NUMBER,
    speed NUMBER,
    memory NUMBER,
    unit_price NUMBER,
    CONSTRAINT fk_product
    FOREIGN KEY (model_no)
    REFERENCES product(model_no)
    );
    the foreign key is added successfully in one of the three tables::
    In other tables when I try to add model_no foreign key using alter syntax
    ALTER TABLE accessories
    add CONSTRAINT fk_product
    FOREIGN KEY(model_no)
    REFERENCES product(model_no);

    I get this error:
    ORA-02264: name already used by an existing constraint

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Well the error message says it all....

    (And please use [code] tags the next time)

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shammat View Post
    "don't work" is not a valid Oracle error message.
    that's funny, we get the same error message in sql server and mysql, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    my car doesn't work.
    tell m how to make my car go.
    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.

  7. #7
    Join Date
    Dec 2011
    Posts
    11

    What kind of answer is that?

    That means, I cannot add foreign key in more than one table using the same primary key reference?
    I want this answer.
    Can I or Can't I?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, you can
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2011
    Posts
    11

    how?

    how?
    I tried to add but got the error as you have already seen.
    How can I add?

  10. #10
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by sachitad View Post
    I tried to add but got the error as you have already seen.
    What about, well, following the error message? It is very descriptive, is not it?
    http://ora-02264.ora-code.com/
    ORA-02264: name already used by an existing constraint
    Cause: The specified constraint name has to be unique.
    Action: Specify a unique constraint name for the constraint.
    Code:
    ALTER TABLE computer
    add CONSTRAINT fk_product
    FOREIGN KEY (model_no)
    REFERENCES product(model_no)
    
    ALTER TABLE accessories
    add CONSTRAINT fk_product
    FOREIGN KEY(model_no)
    REFERENCES product(model_no)
    Quote Originally Posted by sachitad View Post
    How can I add?
    Change your naming convention to make constraint names (those identifiers in bold) different.

Posting Permissions

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