CODEWITHSUNDEEP
sqloracle-databaseoracle12c
prabin baral
prabin baral

Reputation: 3

Getting Error (ORA - 00972) while creating a table

These are the following tables I have created successfully:

CREATE TABLE Olympic_Game 
(
 og_id NUMBER(3) PRIMARY KEY,
 og_type_id NUMBER(3) NOT NULL,
 og_year NUMBER(4) NOT NULL,
 og_website VARCHAR(150),
 og_cancel VARCHAR(1) NOT NULL,
 country_id NUMBER(3) NOT NULL,
 CONSTRAINT check_og_id CHECK (og_id > 0),
 CONSTRAINT check_og_cancel CHECK (og_cancel IN ('Y','N')),
 CONSTRAINT check_og_year_og_type UNIQUE (og_type_id, og_year),
 CONSTRAINT fk_og_type_id FOREIGN KEY(og_type_id) REFERENCES OG_Type(og_type_id),
 CONSTRAINT fk_country_id FOREIGN KEY(country_id) REFERENCES Country(country_id)
);

CREATE TABLE Sport 
(
 sport_id NUMBER(3) PRIMARY KEY,
 sport_title VARCHAR(100) UNIQUE NOT NULL,
 CONSTRAINT check_sport_id CHECK (sport_id > 0)
);

Then I tried to create to another table called Event

CREATE TABLE Event 
(
 event_id NUMBER(6) PRIMARY KEY,
 sport_id NUMBER(3) NOT NULL,
 og_id NUMBER(3) NOT NULL,
 event_title VARCHAR(100) NOT NULL,
 event_team  VARCHAR(1) NOT NULL,
 no_per_team  NUMBER(2) NOT NULL,
 event_gender VARCHAR(1) NOT NULL,
 CONSTRAINT check_event_id CHECK (event_id > 0),
 CONSTRAINT check_event_title_sport_id_og_id_event_team_event_gender UNIQUE (event_title, sport_id, og_id, event_team, event_gender),
 CONSTRAINT check_event_team CHECK (event_team IN ('Y','N')),
 CONSTRAINT check_event_team_no_per_team CHECK ((event_team='N' AND no_per_team=1) OR (event_team='Y' AND no_per_team>1)),
 CONSTRAINT check_event_gender CHECK (event_gender IN ('M','F')),
 CONSTRAINT fk_sport_id FOREIGN KEY(sport_id) REFERENCES Sport(sport_id),
 CONSTRAINT fk_og_id FOREIGN KEY(og_id) REFERENCES Olympic_Game(og_id)
);

But I am getting the following error:

Error report -
SQL Error: ORA-00972: identifier is too long
00972. 00000 -  "identifier is too long"
*Cause:    An identifier with more than 30 characters was specified.
*Action:   Specify at most 30 characters.

Where could I have possibly gone wrong ?

Upvotes: 0

Views: 1337

Answers (2)

Başar Kaya
Başar Kaya

Reputation: 364

Database save identifier name in a system table, so you mustn't top long names.

Upvotes: 0

Hatik
Hatik

Reputation: 1159

The error tells pretty clearly, it seems like check_event_title_sport_id_og_id_event_team_event_gender name of the constraint is too long.

You need to change it to something shorter that is not more than 30 characters long.

Btw the length of your constraint name is 56 characters...

Upvotes: 2

Related Questions