Error when creating table in Oracle Database 11g SQL

Question

I'm new to SQL and I'm trying to create a new table however I get an error when I run my script in the SQL command line, the errors I'm getting are either ORA-00942 missing right parenthesis or ORA-00942 table or view does not exist.

Oh and yes I know I have probably written some terrible script but as mentioned earlier I'm learning so any meaningful criticism would be appreciated along with some help :).

CREATE TABLE Branch
(
Branch_ID varchar(5),
Branch_Name varchar(255),
Branch_Address varchar(255),
Branch_Town varchar(255),
Branch_Postcode varchar(10),
Branch_Phone varchar(50),
Branch_Fax varchar(50),
Branch_Email varchar(50),
Property_ID varchar(5),
Contract_ID varchar(5),
Staff_ID varchar(5),
PRIMARY KEY (Branch_ID),
FOREIGN KEY (Property_ID) REFERENCES Property(Property_Id),
FOREIGN KEY (Contract_ID) REFERENCES Contract(Contract_Id),
FOREIGN KEY (Staff_ID) REFERENCES Staff(Staff_Id)
); 

CREATE TABLE Staff 
(
Staff_ID varchar(5),
Staff_Forename varchar(255),
Staff_Surname varchar(255),
Staff_Address varchar(255),
Staff_Town varchar(255)
Staff_Postcode varchar(10),
Staff_Phone varchar(50),
Staff_DOB varchar(50),
Staff_NIN varchar(10),
Staff_Salary varchar(50),
Staff_Date_Joined varchar(100),
Staff_Viewing_Arranged varchar(100),
Branch_ID varchar(5),
Sales_ID varchar(5),
PRIMARY KEY (Staff_ID),
FOREIGN KEY (Branch_ID) REFERENCES Branch(Branch_ID),
FOREIGN KEY (Sales_ID) REFERENCES Sales(Sales_Id)
);

CREATE TABLE Sales 
(
Sales_ID varchar(5),
Property_Address varchar(255),
Property_Town varchar(255)
Property_Postcode varchar(10),
Property_Type varchar(255),
Num_Rooms varchar(50),
Date_of_Sale varchar(10),
Sales_Bonus varchar(100),
Branch_ID varchar(5),
Property_ID varchar(5),
Staff_ID varchar(5)
Seller_ID varchar(5),
PRIMARY KEY (Sales_ID),
FOREIGN KEY (Branch_ID) REFERENCES Branch(Branch_ID),
FOREIGN KEY (Property_ID) REFERENCES Property(Property_Id),
FOREIGN KEY (Staff_ID) REFERENCES Staff(Staff_Id),
FOREIGN KEY (Seller_ID) REFERENCES Seller(Seller_Id)
);

CREATE TABLE Contract 
(
Contract_ID varchar(5),
Contract_Signed_Date varchar(50),
Property_ID varchar(5),
Buyer_ID varchar(5),
Seller_ID varchar(5),
Branch_ID varchar(5),
PRIMARY KEY (Contract_ID),
FOREIGN KEY (Branch_ID) REFERENCES Branch(Branch_ID),
FOREIGN KEY (Property_ID) REFERENCES Property(Property_Id),
FOREIGN KEY (Seller_ID) REFERENCES Seller(Seller_Id),
FOREIGN KEY (Buyer_ID) REFERENCES Buyer(Buyer_Id)
);

CREATE TABLE Buyer 
(
Buyer_ID varchar(5),
Viewing_Data varchar(255),
Maximum_Budject varchar(255),
Purchase_Price varchar (50),
Buyer_Forename varchar(255),
Buyer_Surname varchar(255),
Buyer_Address varchar(255),
Buyer_Town varchar(255),
Buyer_Postcode varchar(10),
Property_ID varchar(5),
Contract_ID varchar(5),
Survey_ID varchar(5),
PRIMARY KEY (Buyer_ID),
FOREIGN KEY (Property_ID) REFERENCES Property(Property_ID),
FOREIGN KEY (Contract_ID) REFERENCES Contract(Contract_Id),
FOREIGN KEY (Survey_ID) REFERENCES Survey(Survey_Id)
);

CREATE TABLE Seller 
(
Seller_ID varchar(5),
Seller_Forename varchar(255),
Seller_Surname varchar(255),
Seller_Address varchar(255),
Seller_Town varchar(255),
Seller_Postcode varchar(10),
Seller_Property_ID varchar(5),
No_of_Bed varchar(5),
Contract_ID varchar(5),
Property_ID varchar(5),
Sales_ID varchar(5),
PRIMARY KEY (Seller_ID),
FOREIGN KEY (Contract_ID) REFERENCES Contract(Contract_ID),
FOREIGN KEY (Property_ID) REFERENCES Property(Property_Id),
FOREIGN KEY (Sales_ID) REFERENCES Sales(Sales_Id)
);

CREATE TABLE Property 
(
Property_ID varchar(5),
Property_Address varchar(255),
Property_Town varchar(255),
Property_Postcode varchar(10),
Asking_Price varchar(20),
Date_Registered varchar(50),
Property_Fixtures varchar(255),
Size_of_Rooms varchar(100),
Buyer_ID varchar(5),
Staff_ID varchar(5),
Contract_ID varchar(5),
Seller_ID varchar(5),
PRIMARY KEY (Property_ID),
FOREIGN KEY (Buyer_ID) REFERENCES Buyer(Buyer_ID),
FOREIGN KEY (Seller_ID) REFERENCES Seller(Seller_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff(Staff_Id),
FOREIGN KEY (Contract_ID) REFERENCES Contract(Contract_Id)
);

CREATE TABLE Survey 
(
Survey_ID varchar(5),
No_of_Survey varchar(10),
Survey_Type varchar(255),
Organised_By varchar(255),
Property_ID varchar(5),
Staff_ID varchar(5),
Buyer_ID varchar(5),
PRIMARY KEY (Survey_ID),
FOREIGN KEY (Property_ID) REFERENCES Property(Property_ID),
FOREIGN KEY (Staff_ID) REFERENCES Staff(Staff_Id),
FOREIGN KEY (Buyer_ID) REFERENCES Buyer(Buyer_Id)
);

CREATE TABLE Advert
(
Survey_ID Advert_ID varchar(5),
No_of_Adverts varchar(10),
Advert_Website varchar(255),
Advert_Newspaper varchar(255),
Property_ID varchar(5),
PRIMARY KEY (Advert_ID),
FOREIGN KEY (Property_ID) REFERENCES Property(Property_ID)
);

Your script reference tables that haven't been created yet. I.e., look at your first `create table`. by that time, `STAFF` and other tables don't exist yet, but you're setting them as foreign key references. — Marc, Nov 24 '15 at 22:39
You have constraints to tables that are not yet created. First create the tables then add the constraints. Also read what @a_horse_with_no_name has stated, don't have circular foreign key references — vsnyc, Nov 24 '15 at 22:42
Your foreign keys don't make sense. Why does Property reference Buyer? Why does Staff reference Sales and Sales references Staff. You should only have one direction of your foreign keys. — , Nov 24 '15 at 22:43
Ah I see, thanks everyone, you have all really helped and saved me a lot of time! @a_horse_with_no_name -If you saw my conceptual ERD and logical ERD it would make more sense I guess however, I realise that I clearly need to make some more edits! — Display name, Nov 24 '15 at 22:46
@OllyCurtis Looks like you try to put the reference for FK in both Parent and Children table. When you only need it on the Children table — Juan Carlos Oropeza, Nov 24 '15 at 23:21

score 1 · Accepted Answer · answered Nov 24 '15 at 22:40

1

STAFF Table miss a , at the end

Staff_Town varchar(255)

Sales Table too

Property_Town varchar(255) 
Staff_ID varchar(5)

Also you can't define constrains to table not created yet. I could found the error removing those constrain.

answered Nov 24 '15 at 22:40

Juan Carlos Oropeza

47,252
12
78
118

score 0 · Answer 2 · answered Nov 24 '15 at 22:42

Can't define the foreign key references until the table being referred to is created. So, if the foreign key references are circular, in that A => B => C => A, they you will first have to create the tables, then use ALTER TABLE to define the foreign keys. Otherwise, create the tables in order, where first you create a table, then create the table that is referencing it.

score 0 · Answer 3 · answered Nov 24 '15 at 22:43

0

Your script references tables that haven't been created yet. I.e., look at your first create table. by that time, STAFF and other tables don't exist yet, but you're setting them as foreign key references.

You should create your tables first, and then apply constraints after the dependencies are in place, using alter:

ALTER TABLE BRANCH
ADD CONSTRAINT fk_staff_id
  FOREIGN KEY (Staff_ID) REFERENCES Staff(Staff_Id);

answered Nov 24 '15 at 22:43

Marc

11,403
2
35
45

Also might consider INT over varchar for your keys, see http://stackoverflow.com/questions/19299874/can-i-use-varchar-as-the-primary-key – Aaron Dietz Nov 24 '15 at 22:45

score 0 · Answer 4 · answered Nov 24 '15 at 22:45

because you have tables that references each other you need create the references after the tables are created

--create table Buyer
CREATE TABLE Buyer 
(
Buyer_ID varchar(5),
Property_ID varchar(5),
PRIMARY KEY (Buyer_ID)
);

--create table Property
CREATE TABLE Property 
(
Property_ID varchar(5),
Buyer_ID varchar(5),
PRIMARY KEY (Property_ID)
);

--now you can set your reference constraints
ALTER TABLE Property Add FOREIGN KEY (Buyer_ID) REFERENCES Buyer(Buyer_ID);
ALTER TABLE Buyer Add FOREIGN KEY (Property_ID) REFERENCES Property(Property_ID);

score 0 · Answer 5 · answered Nov 24 '15 at 22:48

0

Your syntax is not clear on your keys.

Here is an example of what a create table with a primary key and a foreign key (fk) would look like:

 CREATE TABLE animals (
             animal_id             NUMBER(10) PRIMARY KEY,
             animal_name           VARCHAR2(50) NOT NULL,
             animal_species_code   NUMBER(50) NOT NULL
    CONSTRAINT fk_animal_species REFERENCES animal_species
                         (species_code));

answered Nov 24 '15 at 22:48

tale852150

1,618
3
17
23

it's not going to work because `animal_species` in OP's script contains reference to `animals` – are Nov 24 '15 at 22:51
this was just an example based on a fictitious set of tables, animals and animal_species. – tale852150 Nov 25 '15 at 21:59

Bob Jarvis - Слава Україні · Answer 6 · 2015-11-24T23:19:33.213

It appears that the modeling in this set of tables might benefit from some additions and changes.

As an example, the STAFF table references the BRANCH table - but BRANCH also references STAFF. This means that BRANCH can only reference one STAFF, and vice versa, which I suspect is not what's wanted. It appears that you want a many-to-many relationship between BRANCH and STAFF, and the standard solution for enabling this is a junction table - one which contains the primary keys of both tables. Such a junction table between BRANCH and STAFF might look like:

CREATE TABLE BRANCH_STAFF
  (BRANCH_ID  VARCHAR(5)
     CONSTRAINT BRANCH_STAFF_FK1
       REFERENCES BRANCH(BRANCH_ID),
   STAFF_ID   VARCHAR(5)
     CONSTRAINT BRANCH_STAFF_FK2
       REFERENCES STAFF(STAFF_ID),
   CONSTRAINT PK_BRANCH_STAFF
     PRIMARY KEY (BRANCH_ID, STAFF_ID)
     USING INDEX);

Best of luck.

Error when creating table in Oracle Database 11g SQL

6 Answers6