Beginner SQL: Referencing non primary keys

Question

I have created this table.

CREATE TABLE TourEvent(
TourID VARCHAR(7),
TourName references WineryTours(TourName),
Month CHAR (3),
Day NUMERIC (2),
Year NUMERIC (4),
Fee NUMERIC (4),
PRIMARY KEY(TourID)
);

I am trying to create another table that references pretty much all of this table into another one but i am running into trouble with

ORA-02270: no matching unique or primary key for this column-list

This is the code i have so far

CREATE TABLE Bookings(
BookingID VARCHAR(7) PRIMARY KEY,
ClientID references Clients(ClientID),
TourID references TourEvent(TourID),
Tour references TourEvent(TourName),
EventMonth references TourEvent(MONTH),
EventDay references TourEvent(DAY),
EventYear references TourEvent(Year),
Fee references TourEvent(Fee),
DateBooked Date
);

Reading about foreign keys it says i only can reference primary keys and i am trying different things but have no idea how to go on.

Thank you

Are you really using MySQL? (That error code is more Oracle like...) — jarlh, May 23 '17 at 12:50
Primary key by definition is unique and not null. Think of them as the envelope containing all the data for a record. So if you reference the envelope for TourEvent in your bookings table, you have no need for all the other data because the envelope contains all the other data. Now, if you need to replicate the data because say TourEvents are just "events" and they could be repeated on different dates with slightly different attributes, you can use the envelope data as defaults and bookings contains data which overrides those defaults. (but the other data can't be keys) — xQbert, May 23 '17 at 12:56
"I am trying to create another table that references pretty much all of this table into another one but i am running into trouble with" Why? For what benefit? What are you trying to accomplish? What is the business need/Requirement you're trying to achieve? — xQbert, May 23 '17 at 12:58
@xQbert it is a university assignment. There was sampled data i had to add into the table and the dates were one of them. — Damien Lim, May 23 '17 at 13:13

score 1 · Answer 1 · answered May 23 '17 at 12:51

1

Indeed you cannot add a foreign key to a non primary key field.

If you want the foreign key to the TourID field in TouEvent table, you will need to create a primary key for it.

answered May 23 '17 at 12:51

Lucas Neves

59
1
5

score 1 · Accepted Answer · answered May 23 '17 at 12:58

You have the wrong approach. You want to include only TourId as the reference. Something like this:

CREATE TABLE Bookings (
    BookingID VARCHAR2(7) PRIMARY KEY,
    ClientID ?? references Clients(ClientID),
    TourID VARCHAR2(7) references TourEvent(TourID),
    DateBooked Date
);

You can get the details of the tour by using a JOIN when you query the database.

Notes:

You need to include the type when defining a column, even a foreign reference.
Oracle (still) recommends VARCHAR2() over VARCHAR().
When you define a foreign key reference, the type of the column(s) and keys must match.

Thank you for this. Ill go with this if nothing else works. It is an assignment and the data for the bookings table included the same columns. Is there really no other way to go about this? — Damien Lim, May 23 '17 at 13:15

score 1 · Answer 3 · answered May 23 '17 at 13:12

In the TourEvent table, you can replace DAY, MONTH and YEAR columns with a single DATE column and the TourName column needs a data type:

CREATE TABLE TourEvent(
  TourID    VARCHAR2(7)  CONSTRAINT TourEvent__TourID__PK   PRIMARY KEY,
  TourName  VARCHAR2(50) CONSTRAINT TourEvent__TourName__FK REFERENCES WineryTours(TourName),
  eventdate DATE,
  Fee       NUMERIC (4)
);

It is also good practice to name your constraintsand to use VARCHAR2 rather than VARCHAR.

You then need to reference only the primary keys:

CREATE TABLE Bookings(
  BookingID  VARCHAR2(7) CONSTRAINT Bookings__ID__PK PRIMARY KEY,
  ClientID   VARCHAR2(7) CONSTRAINT Bookings__ClientID__FK references Clients(ClientID),
  TourID     VARCHAR2(7) CONSTRAINT Bookings__TourID__FK references TourEvent(TourID),
  DateBooked Date
);

If you want to find the details of the tour you can use a join:

SELECT b.*,
       t.tourname,
       t.eventdate,
       t.fee
FROM   Bookings b
       INNER JOIN
       TourEvent t
       ON ( b.tourid = t.tourid )

Beginner SQL: Referencing non primary keys

3 Answers3