cant reference sport CHAR(6) to other column error-CodePudding

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)
);

CodePudding user response：

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