If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Trouble assigning foreign key

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 12-31-11, 07:28
sachitad sachitad is offline
Registered User
 
Join Date: Dec 2011
Posts: 11
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.
Reply With Quote
  #2 (permalink)  
Old 12-31-11, 08:00
shammat shammat is offline
Registered User
 
Join Date: Nov 2003
Posts: 2,408
"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
Reply With Quote
  #3 (permalink)  
Old 12-31-11, 09:35
sachitad sachitad is offline
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old 12-31-11, 09:36
shammat shammat is offline
Registered User
 
Join Date: Nov 2003
Posts: 2,408
Well the error message says it all....

(And please use [code] tags the next time)
Reply With Quote
  #5 (permalink)  
Old 12-31-11, 10:26
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,540
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #6 (permalink)  
Old 12-31-11, 10:39
anacedent anacedent is offline
Registered User
 
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,416
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.
Reply With Quote
  #7 (permalink)  
Old 12-31-11, 22:25
sachitad sachitad is offline
Registered User
 
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?
Reply With Quote
  #8 (permalink)  
Old 12-31-11, 22:52
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,540
yes, you can
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #9 (permalink)  
Old 12-31-11, 23:51
sachitad sachitad is offline
Registered User
 
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?
Reply With Quote
  #10 (permalink)  
Old 01-01-12, 02:17
flyboy flyboy is offline
Registered User
 
Join Date: Mar 2007
Posts: 546
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/
Quote:
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.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On