Multiple errors dropping and creating tables

Question

I'm not good at writing create scripts. And the SQL Developer error messages don't help me. I'm looking for someone to help me figure out what's wrong because I can't do this alone.

Here is my code:

/*Drops */
DROP TABLE Accountsite CASCADE CONSTRAINTS;
DROP TABLE Player CASCADE CONSTRAINTS;
DROP TABLE Stream CASCADE CONSTRAINTS;
DROP TABLE Server CASCADE CONSTRAINTS;
DROP TABLE Activegame CASCADE CONSTRAINTS;
DROP TABLE Livegame CASCADE CONSTRAINTS;
DROP TABLE Toplist CASCADE CONSTRAINTS;
DROP TABLE Champion CASCADE CONSTRAINTS;
DROP TABLE Skin CASCADE CONSTRAINTS;
DROP TABLE Sale CASCADE CONSTRAINTS;
DROP TABLE PlayerServer CASCADE CONSTRAINTS;
DROP TABLE ActiveServer CASCADE CONSTRAINTS;

/*Creates */
CREATE TABLE Accountsite(
AccountID NUMBER PRIMARY KEY NOT NULL,
PasswordAcc VARCHAR(20) NULL,
Email VARCHAR(20) NULL,
Playername VARCHAR(20) NULL,
FOREIGN KEY(Playername) REFERENCES Player (Playername));

CREATE TABLE Player(
PlayerID NUMBER PRIMARY KEY,
Mostplayed VARCHAR(20) NOT NULL,
RankID NUMBER(10) NOT NULL,
Playername VARCHAR(20) NOT NULL);

CREATE TABLE Stream(
StreamID NUMBER PRIMARY KEY,
StreamAdress VARCHAR(20) NOT NULL,
Playername VARCHAR(20) NOT NULL,
FOREIGN KEY(Playername) REFERENCES Player (Playername));

CREATE TABLE Server(
ServerID NUMBER PRIMARY KEY,
Servername VARCHAR(20));

CREATE TABLE Activegame(
GameID NUMBER PRIMARY KEY);

CREATE TABLE Livegame(
SpectateID NUMBER PRIMARY KEY);

CREATE TABLE Toplist(
ToplistID NUMBER PRIMARY KEY,
ToplistFunction VARCHAR(20) NOT NULL,
Playername VARCHAR(20) NOT NULL,
Championname VARCHAR(20) NOT NULL,
FOREIGN KEY(Playername) REFERENCES Player (Playername),
FOREIGN KEY(Champion) REFERENCES Champion (Champion));

CREATE TABLE Champion(
ChampionID NUMBER PRIMARY KEY,
Championname VARCHAR(20) NOT NULL,
Championskill1 VARCHAR(20) NOT NULL,
Championskill2 VARCHAR(20) NOT NULL,
Championskill3 VARCHAR(20) NOT NULL,
Championskill4 VARCHAR(20) NOT NULL,
Championcost NUMBER(10) DEFAULT(6300),
SkinID NUMBER(10) NOT NULL,
SaleID NUMBER(10) NOT NULL,
FOREIGN KEY(SkinID) REFERENCES Skin (SkinID),
FOREIGN KEY(SaleID) REFERENCES Sale (SaleID));

CREATE TABLE Skin(
SkinID NUMBER PRIMARY KEY,
Skinname VARCHAR(20) NOT NULL,
Skincost NUMBER(10) NOT NULL);

CREATE TABLE Sale(
SaleID NUMBER PRIMARY KEY);

CREATE TABLE PlayerServer(
Playername VARCHAR(20) NOT NULL,
ServerIDPlayer NUMBER NOT NULL,
FOREIGN KEY(Playername) REFERENCES Player (Playername),
FOREIGN KEY(ServerIDPlayer) REFERENCES Server (ServerID),
Constraint PlayerserverID PRIMARY KEY (Playername, ServerIDPlayer));

CREATE TABLE ActiveServer(
GameIDServer NUMBER NOT NULL,
ServerIDGame NUMBER NOT NULL,
FOREIGN KEY(GameIDServer) REFERENCES Acrivegame (GameID),
FOREIGN KEY(ServerIDGame) REFERENCES Server (ServerID),
Constraint ActiveserverID PRIMARY KEY (GameIDServer, ServerIDGame));

commit;

And the errors are as follows:

Error starting at line : 2 in command -
DROP TABLE Accountsite CASCADE CONSTRAINTS
Error report -
SQL Error: ORA-00942: Tabel of view bestaat niet.
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Table PLAYER dropped.

Error starting at line : 4 in command -
DROP TABLE Stream CASCADE CONSTRAINTS
Error report -
SQL Error: ORA-00942: Tabel of view bestaat niet.
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Table SERVER dropped.

Table ACTIVEGAME dropped.

Table LIVEGAME dropped.

Error starting at line : 8 in command -
DROP TABLE Toplist CASCADE CONSTRAINTS
Error report -
SQL Error: ORA-00942: Tabel of view bestaat niet.
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Error starting at line : 9 in command -
DROP TABLE Champion CASCADE CONSTRAINTS
Error report -
SQL Error: ORA-00942: Tabel of view bestaat niet.
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Table SKIN dropped.

Table SALE dropped.

Error starting at line : 12 in command -
DROP TABLE PlayerServer CASCADE CONSTRAINTS
Error report -
SQL Error: ORA-00942: Tabel of view bestaat niet.
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Error starting at line : 13 in command -
DROP TABLE ActiveServer CASCADE CONSTRAINTS
Error report -
SQL Error: ORA-00942: Tabel of view bestaat niet.
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Error starting at line : 16 in command -
CREATE TABLE Accountsite(
AccountID NUMBER PRIMARY KEY NOT NULL,
PasswordAcc VARCHAR(20) NULL,
Email VARCHAR(20) NULL,
Playername VARCHAR(20) NULL,
FOREIGN KEY(Playername) REFERENCES Player (Playername))
Error report -
SQL Error: ORA-00942: Tabel of view bestaat niet.
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Table PLAYER created.

Error starting at line : 29 in command -
CREATE TABLE Stream(
StreamID NUMBER PRIMARY KEY,
StreamAdress VARCHAR(20) NOT NULL,
Playername VARCHAR(20) NOT NULL,
FOREIGN KEY(Playername) REFERENCES Player (Playername))
Error report -
SQL Error: ORA-02270: Geen overeenkomende unieke of primaire sleutel voor deze kolomlijst.
02270. 00000 -  "no matching unique or primary key for this column-list"
*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
           gives a column-list for which there is no matching unique or primary
           key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
           catalog view

Table SERVER created.

Table ACTIVEGAME created.

Table LIVEGAME created.

Error starting at line : 45 in command -
CREATE TABLE Toplist(
ToplistID NUMBER PRIMARY KEY,
ToplistFunction VARCHAR(20) NOT NULL,
Playername VARCHAR(20) NOT NULL,
Championname VARCHAR(20) NOT NULL,
FOREIGN KEY(Playername) REFERENCES Player (Playername),
FOREIGN KEY(Champion) REFERENCES Champion (Champion))
Error report -
SQL Error: ORA-00904: "CHAMPION": ongeldige ID
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

Error starting at line : 53 in command -
CREATE TABLE Champion(
ChampionID NUMBER PRIMARY KEY,
Championname VARCHAR(20) NOT NULL,
Championskill1 VARCHAR(20) NOT NULL,
Championskill2 VARCHAR(20) NOT NULL,
Championskill3 VARCHAR(20) NOT NULL,
Championskill4 VARCHAR(20) NOT NULL,
Championcost NUMBER(10) DEFAULT(6300),
SkinID NUMBER(10) NOT NULL,
SaleID NUMBER(10) NOT NULL,
FOREIGN KEY(SkinID) REFERENCES Skin (SkinID),
FOREIGN KEY(SaleID) REFERENCES Sale (SaleID))
Error report -
SQL Error: ORA-00942: Tabel of view bestaat niet.
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Table SKIN created.

Table SALE created.

Error starting at line : 74 in command -
CREATE TABLE PlayerServer(
Playername VARCHAR(20) NOT NULL,
ServerIDPlayer NUMBER NOT NULL,
FOREIGN KEY(Playername) REFERENCES Player (Playername),
FOREIGN KEY(ServerIDPlayer) REFERENCES Server (ServerID),
Constraint PlayerserverID PRIMARY KEY (Playername, ServerIDPlayer))
Error report -
SQL Error: ORA-02270: Geen overeenkomende unieke of primaire sleutel voor deze kolomlijst.
02270. 00000 -  "no matching unique or primary key for this column-list"
*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
           gives a column-list for which there is no matching unique or primary
           key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
           catalog view

Error starting at line : 81 in command -
CREATE TABLE ActiveServer(
GameIDServer NUMBER NOT NULL,
ServerIDGame NUMBER NOT NULL,
FOREIGN KEY(GameIDServer) REFERENCES Acrivegame (GameID),
FOREIGN KEY(ServerIDGame) REFERENCES Server (ServerID),
Constraint ActiveserverID PRIMARY KEY (GameIDServer, ServerIDGame))
Error report -
SQL Error: ORA-00942: Tabel of view bestaat niet.
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:

Commit complete.

What am I doing wrong?

This question contains too many errors to be treated at once. I suggest trying to understand each statement separately by executing them one after another and trying to deal with each error on its own. For example - the first errors in your script are of `table or view does not exist` type, so you can understand that the reason for them is trying to drop a table that doesn't exist. After you've screened out all successful statements and tried solving some of the errors yourself you should post a question for an individual statement and it's error. — Yaron Idan, Apr 09 '16 at 10:46
In this case ignore the errors from the drop statements, they're expected since the creations are failing. Some of the errors in the creations are because earlier ones failed, so start with the first non-drop error and figure that out. Which is the ORA-02270 creating Stream; which is fairly self-explanatory. (Hint: that table should not have the player *name* stored at all). — Alex Poole, Apr 09 '16 at 11:10
Er, OK, that isn't the first one, it's hard to see in the unformatted dump of all the errors. Look at the order you are creating the tables, and which other tables they refer to. When you create Accountsite, does Player exist yet? That has the same issue storing the player name too. — Alex Poole, Apr 09 '16 at 11:13

score 0 · Answer 1 · edited May 23 '17 at 12:07

There are several issues with your script. Basically when you include foreign key constraints, they have to be pointing to a unique column which is in a table that already exists (has to be created before the referencing table).

Errors on trying to drop a table

You basically get an ORA-00942 because your table does not exist, thus it cannot be dropped. As a workaround you could catch that exception. It has been described in this answer.

Errors on trying to create a table

You have several issues in this topic. First one is when you execute the create statement of table Accountsite. You also get an ORA-00942 here, because table Player which you are trying to reference in foreign key constraint does not exist. You need to create it before Accountsite, so that it can reference something that exists.

Next error occurs while creating a table Stream. It's the ORA-02270. You cannot reference a column from another table which is not unique. You need to make Playername column unique in table Player. Note that when you fix the first error with Accountsite table, you would have the same error for this table as for Stream, since you are trying to reference a column which is not unique in both create table statements (so you also need to fix the second error and it will go without errors).

Now the Toplist table. There is no Champion column in your Champion table. You probably mean Championname. In this case also Champion table needs to be created before the Toplist table, since you cannot create a foreign key constraint to a non-existing table. You also need to declare your Championname column in Champion table unique (the very same like you do with Playername in table Player).

In Champion you reference a Sale and Skin table - they both need to be created before Champion table (see explanation above).

As for PlayerServer the error will solve itself when you create a unique constraint on Player(Playername).

In ActiveServer you have a typo in referencing "Acrivegame" which I believe should be Activegame.

Surely stream etc. should store player ID, not name? A name may well not be unique anyway, but even if it is, duplicating the name breaks normalisation. The ID is handy to use as an FK already. Same with champion name. — Alex Poole, Apr 09 '16 at 11:47
Are you giving me hints on _not mine_ design? I'm merely helping with errors. — Kamil Gosciminski, Apr 09 '16 at 12:11

Multiple errors dropping and creating tables

1 Answers1