ERROR 1068 (42000): Multiple primary key define

Question

I would like to create the following SQL tables using mySql.

CYCLIST (CID, Name, Surname, Nationality, TID, BirthYear)

TEAM (TID, NameT, YearFoundation, OfficeLocation*)

STAGE (Edition, SID, DepartureCity, ArrivalCity, Length, DeltaHeight, Difficulty)

INDIVIDUAL_RANKING (CID, SID, Edition, Location)

Note: Italic fields identify the primary key of each relationship while asterisks indicate optional fields.

Some details about the fields of the various tables are given below:

• SID is an incremental positive integer (1, 2, etc.).

• Edition is an incremental positive integer (1, 2, etc.).

• Length is expressed in meters.

• Location contains the arrival position of the cyclist in that stage (1, 2, etc.).

• Difficulty is a positive integer between 1 and 10.

• Difference in height is expressed in meters.

• YearFoundation and YearBirth are whole values between 1900 and 2000.

• All text fields have a maximum length of 50 characters.

Code Written for the last table:

CREATE TABLE INDIVIDUAL_RANKING (

SID INT PRIMARY KEY NOT NULL REFERENCES STAGE(CID),

CID INT PRIMARY KEY NOT NULL REFERENCES CYCLIST(CID) ,

Edition INT PRIMARY KEY NOT NULL REFERENCES STAGE(Edition),

Location CHAR(50) NOT NULL

);

Error:

ERROR 1068 (42000): Multiple primary key defined

score 0 · Accepted Answer · answered Jun 02 '22 at 18:16

Ali,

A table can only have one primary key defined on it. Your Foreign Keys do no need to be Primary. You can however have indexes and constraints on them. You can create a surrogate key, which is a new column (like an identity column) that serves as the column that uniquely identifies the row and is the Primary Key if you do not have a natural Primary Key.

ERROR 1068 (42000): Multiple primary key define

1 Answers1