CODEWITHSUNDEEP
oracle-databaseddl
user2714726
user2714726

Reputation: 29

Oracle Create TABLE

I am trying to create a table with foreign key using oracle. My syntax is as follows

CREATE TABLE product (
   product_id       INT(7) NOT NULL,
   supplier_id      INT(7) NOT NULL,
   product_name     VARCHAR2(30),
   product_price    DOUBLE(4),
   product_category VARCHAR2(30),
   product_brand    VARCHAR2(20),
   product_expire   DATE,
   PRIMARY KEY (product_id),
   FOREIGN KEY (supplier_id)
)

I got a error, saying

Error at Command Line:2 Column:14 Error report: SQL Error: ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis" *Cause:
*Action:

Please help!

Upvotes: 1

Views: 17816

Answers (3)

Indu lakshmi s
Indu lakshmi s

Reputation: 11

You create like foreign key references parent table that is the proper syntax for creating the foreign key

CREATE TABLE product(
   product_id       number(7) NOT NULL,
   supplier_id      number(7) NOT NULL,
   product_name     VARCHAR(30),
   product_price    DOUBLE PRECISION,
   product_category VARCHAR(30),
   product_brand    VARCHAR(20),
   product_expire   DATE,
   PRIMARY KEY (product_id),
   FOREIGN KEY (supplier_id)
    REFERENCES parent_table (supplier_id)
);

Upvotes: 1

Sagar Joon
Sagar Joon

Reputation: 1417

You should not use limit for int type...oracle will take default length for int type . Instead of int you can use Number type to make it run. And DOUBLE PRECISION is a data type in oracle but Double is not there. Also , syntax for foreign key is wrong. so this query will work for sure :

CREATE TABLE product(
   product_id       number(7) NOT NULL,
   supplier_id      number(7) NOT NULL,
   product_name     VARCHAR2(30),
   product_price    DOUBLE PRECISION,
   product_category VARCHAR2(30),
   product_brand    VARCHAR2(20),
   product_expire   DATE,
   PRIMARY KEY (product_id),
   FOREIGN KEY (supplier_id)
    REFERENCES parent_table (supplier_id)
);

Upvotes: 2

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

Your foreign key should refference another column on another table.

  1. Here is the documentation you need to fix your issue (how to write the query with the correct syntax for foreign key)

  2. Also, change your data type for column product_price from DOULBE(4) to NUMBER(12,4).

Upvotes: 3

Related Questions