cant reference sport CHAR(6) to other column error

Question

As the title says, for some reason it gives me the error that the sport variable cant reference to another column when I am not even referencing it, any reason for this?

CREATE TABLE club_rates
(club_id NUMBER(4)
CONSTRAINT rates_club_fk REFERENCES club_subscriptiontypes(club_id,subscriptiontype)
,sport CHAR(6)
,subscriptiontype CHAR(11)
,subscription_startdate DATE
,subscription_rate_existing NUMBER(2)
,subscription_rate_new NUMBER(2)
,subscription_enddate DATE
,registration_startdate DATE
,registration_enddate DATE,
CONSTRAINT rates_clubsportsub_pk PRIMARY KEY (club_id, sport, subscriptiontype, subscription_startdate)
);

Did you mean *ORA-02256: number of referencing columns must match referenced columns*? — William Robertson, Dec 24 '21 at 13:24
Also, [`char` is usually a mistake in Oracle](https://stackoverflow.com/a/42165653/230471). — William Robertson, Dec 24 '21 at 16:02

score 1 · Accepted Answer · answered Dec 24 '21 at 13:23

Assuming you have the table:

CREATE TABLE club_subscriptiontypes(
  club_id          NUMBER(4),
  subscriptiontype CHAR(11),
  PRIMARY KEY (club_id,subscriptiontype)
);

Then you do not want to declare a composite primary key column inline against a single column or you get the error:

ORA-02256: number of referencing columns must match referenced columns

Instead, declare it out-of-line at the end of the statement:

CREATE TABLE club_rates(
  club_id                    NUMBER(4)
, sport                      CHAR(6)
, subscriptiontype           CHAR(11)
, subscription_startdate     DATE
, subscription_rate_existing NUMBER(2)
, subscription_rate_new      NUMBER(2)
, subscription_enddate       DATE
, registration_startdate     DATE
, registration_enddate       DATE
, CONSTRAINT rates_club_fk
    FOREIGN KEY (club_id, subscriptiontype)
    REFERENCES club_subscriptiontypes(club_id,subscriptiontype)
, CONSTRAINT rates_clubsportsub_pk
    PRIMARY KEY (club_id, sport, subscriptiontype, subscription_startdate)
);

As an aside, you probably don't want to use fixed-length CHAR strings and, instead, want variable-length VARCHAR2 strings.

db<>fiddle here

cant reference sport CHAR(6) to other column error

1 Answers1