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,
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 1068 (42000): Multiple primary key defined