2 Replies - 2984 Views - Last Post: 29 September 2012 - 03:12 PM

#1 kosmro  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 61
  • Joined: 07-March 12

Multiple column referencing on column error

Posted 28 September 2012 - 06:47 PM

Well, I am hopeing I havent' repeated a topic here, but here goes.

I am trying to create a script to create a table with has the following attributes 'medal_events(day, sport#*, gold_win*, silver_win*, bronze_win*)'
Anything with '#' is a primary Key, and anything with '*' is a foreign key. Now there are three foreign keys which need to reference the same data column in a table named 'athletes', and it is the PK of that table which is being referenced.

The error I'm getting I have spooled and outputted below:
SQL> @create_medal_events
 REFERENCES athletes (ath_code))
                               *
ERROR at line 11:
ORA-02256: number of referencing columns must match referenced columns 


SQL> spool off



And the actual script that I have written which creates this error every time I try to run it is as follows:
CREATE TABLE medal_events
(day          DATE,
 sport        VARCHAR2(7) NOT NULL,
 gold_win     VARCHAR2(7),
 silver_win   VARCHAR2(7),
 bronze_win   VARCHAR2(7),
 CONSTRAINT pk_medal_events PRIMARY KEY (sport),
 CONSTRAINT fk_sports FOREIGN KEY (sport)
 REFERENCES sports (spo_code),
 CONSTRAINT fk_athletes FOREIGN KEY (gold_win, silver_win, bronze_win)
 REFERENCES athletes (ath_code));




Some help with this would be most appreciated!
thanks.

Is This A Good Question/Topic? 0
  • +

Replies To: Multiple column referencing on column error

#2 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,594
  • Joined: 16-October 07

Re: Multiple column referencing on column error

Posted 29 September 2012 - 02:48 AM

You've said your foreign key is three fields (gold_win, silver_win, bronze_win) and you're trying to match it to one field (ath_code).

You need three constraints, e.g.:
CONSTRAINT fk_athletes_1 FOREIGN KEY (gold_win)
   REFERENCES athletes (ath_code),
CONSTRAINT fk_athletes_2 FOREIGN KEY (silver_win)
   REFERENCES athletes (ath_code),



Why varchar(7) for everything?!? It's an odd kind of key. I'd expect your athlete would have an integer primary key. Also, 7 for sport seems little limiting, you can't even fit volley ball.
Was This Post Helpful? 1
  • +
  • -

#3 kosmro  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 61
  • Joined: 07-March 12

Re: Multiple column referencing on column error

Posted 29 September 2012 - 03:12 PM

It worked! thanks baavgai.

The reason for varcahr2(7) for everything, is that they are all 7 lettered codes which are the primary keys for each sport or athlete. This is done as there are multiple divisions of each sport (eg: Swimming 100m, 200, etc), so to prevent clashes, each has an individual code which can only relate to it (eg Swimming Mens Freestyle 200m = SSFM200 = Sports/Swimming/Freestyle/Male/200m), and it is essentially the same with the athletes. I thought this would be easier to do as it means I can easily identify when inputting data or querying data.


Anyway, thanks for your help baavgai.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1