CODEWITHSUNDEEP
databaseoracle-databasecommandcreate-table
gymcode
gymcode

Reputation: 4623

Oracle create table Invalid Name / Constraint

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)
);

Upvotes: 0

Views: 260

Answers (2)

Husqvik
Husqvik

Reputation: 5809

BOOLEAN is not valid SQL type. Use NUMBER(1) with 0 and 1, CHAR(1) or VARCHAR2(1) with 'Y' and 'N' values or other surrogate representation.

Upvotes: 1

jarlh
jarlh

Reputation: 44776

You mixed up the two different ways to specify foreign keys. And had an extra comma.

CREATE TABLE Registration
(
registrationID varchar2(5) NOT NULL CONSTRAINT registrationID PRIMARY KEY,
competitionID varchar2(5) NOT NULL CONSTRAINT competitionIDfk  REFERENCES Competition(competitionID),
competitorID varchar2(5) NOT NULL CONSTRAINT competitorIDfk  REFERENCES Competitor(competitorID)
)

Foreign keys can either be specified per column:

columnname datatype CONSTRAINT constraintname REFERENCES tablename [ (column) ]

Or per table:

CONSTRAINT constraintname FOREIGN KEY (column-list) REFERENCES tablename [ (column-list) ]

Upvotes: 1

Related Questions