Where is the wrong logic in my Create Table statements?

Question

I'm trying to run these statements and I get an error saying no matching unique or primary key for this column-list. Can you please help me how to fix this problem?

I get the problem when I try to create table SITE:

CREATE TABLE OLMP_COUNTRY (
NOC CHAR(3),
TEAM VARCHAR2(100),
CITY VARCHAR2(100),
CONSTRAINT country_pk PRIMARY KEY(NOC)
);

CREATE TABLE ATHLETE (
ATHELTE_ID CHAR(8),
NAME VARCHAR2(100),
AGE CHAR(3),
SEX CHAR(1),
HEIGHT CHAR(3), 
WEIGHT DECIMAL(3,1), 
NOC CHAR(3), 
CONSTRAINT athlete_pk PRIMARY KEY(ATHLETE_ID), 
CONSTRAINT country_fk FOREIGN KEY(NOC) REFERENCES OLMP_COUNTRY(NOC) 
); 

CREATE TABLE SITE (
NOC CHAR(3),
CITY VARCHAR2(100),
SEASON VARCHAR2(20),
YEAR CHAR(4),
CONSTRAINT site_pk PRIMARY KEY(NOC),
CONSTRAINT country_fk FOREIGN KEY(CITY) REFERENCES OLMP_COUNTRY(CITY)
);

CREATE TABLE RESULTS (
RESULT_ID CHAR(8),
MEDAL CHAR(6),
ATHLETE_ID CHAR(8),
SPORT_EVENT VARCHAR2(100), 
YEAR CHAR(4), 
GAMES VARCHAR2(50), 
CONSTRAINT results_pk PRIMARY KEY(RESULTS_ID) 
);

CREATE TABLE EVENT (
SPORT_EVENT VARCHAR2(100), 
SPORT VARCHAR2(50),
GAMES VARCHAR2(50) 
CONSTRAINT event_pk PRIMARY KEY(SPORT_EVENT)
);

What did you learn reading all answers of many questions found googling your error message, and the manual re FKs? [ask] — philipxy, Feb 15 '19 at 21:35
By the way, the standard type for strings in Oracle is `VARCHAR2`. You should only use the highly specialised [`CHAR`](https://stackoverflow.com/q/20417845/230471) type in the case where you want to add blank spaces up to the specified length, which is to say, never. — William Robertson, Feb 16 '19 at 23:12

Gordon Linoff · Accepted Answer · 2019-02-15T15:17:28.447

2

A foreign key should be referencing the primary key of the table it is referring to.

So I think you want:

CREATE TABLE SITE (
    NOC CHAR(3),
    CITY VARCHAR2(100),
    SEASON VARCHAR2(20),
    YEAR CHAR(4),
    CONSTRAINT site_pk PRIMARY KEY(NOC),
    CONSTRAINT site_country_fk FOREIGN KEY(NOC) REFERENCES OLMP_COUNTRY(NOC)
);

I have no idea why you are repeating CITY in both tables, but the foreign key constraint should be to the primary key. You can look up the city using JOIN. It should not be repeated.

edited Feb 15 '19 at 15:17

answered Feb 15 '19 at 14:17

Gordon Linoff

1,242,037
58
646
786

3

"...referencing the primary key of the table..." -- For completeness I would add: ...or a column with a `UNIQUE` constraint. – The Impaler Feb 15 '19 at 14:29
I actually got that answer from someone else helping me on another site.. Now it makes sense. Also I ran the updated statement and it says name is already used by an existing constraint? So do I have to just rename the country_fk? – ThatOneGoi Feb 15 '19 at 14:31
@Impaler . . . I'm not trying to complete. I'm trying to give good advice on how databases are created. They key reference should be to a primary key. – Gordon Linoff Feb 15 '19 at 15:16
*"and it says name is already used by an existing constraint? "* - yes, you've already used it on ATHLETE table. This is what a common convention for naming foreign keys is to use the child table and parent table; e.g. `athlete_country_fk`, 'site_country_fk` – APC Feb 15 '19 at 15:16

Where is the wrong logic in my Create Table statements?

1 Answers1