I met an error in an SQL command when trying to create a table in SQL. Below is my command:
CREATE TABLE Registration
(
registrationID varchar2(5) NOT NULL CONSTRAINT registrationID PRIMARY KEY,
competitionID varchar2(5) NOT NULL CONSTRAINT competitionID REFERENCES Competition(competitionID),
competitorID varchar2(5) NOT NULL CONSTRAINT competitorID REFERENCES Competitor(competitorID),
categoryType varchar2(6) NOT NULL,
entryFeeStatus char(1) NOT NULL,
creditCardNumber number(16),
datePaid date
);
My competitionID is primary key of Competition table. My competitorID is primary key of Competitor table.
The error shown is:
Error report -
SQL Error: ORA-02264: name already used by an existing constraint
02264. 00000 - "name already used by an existing constraint"
*Cause: The specified constraint name has to be unique.
*Action: Specify a unique constraint name for the constraint.
May I know what I should change in my statement? Thank you.
Below are the Competition and Competitor tables that I created:
CREATE TABLE Competition
(
competitionID varchar2(5) NOT NULL CONSTRAINT competitionID PRIMARY KEY,
timePlanned date NOT NULL,
country varchar2(50) NOT NULL,
city varchar2(50),
address varchar2(50),
entryFee number(4) NOT NULL
);
CREATE TABLE Competitor
(
competitorID varchar2(5) NOT NULL CONSTRAINT competitorID PRIMARY KEY,
firstName varchar2(9) NOT NULL,
lastName varchar2(9) NOT NULL,
dateOfBirth date NOT NULL,
nationality varchar2(12),
gender varchar2(1) NOT NULL,
lifetimeRanking number(6),
totalPrizeMoney number(6)
);
