CODEWITHSUNDEEP
oracle-databaseforeign-keysddl
Jagadish Patnala
Jagadish Patnala

Reputation: 1

While creating table in oracle database it is showing right parenthesis missing error

While creating table in oracle database it is showing right parenthesis missing error

CREATE TABLE Catalog(
sid VARCHAR2(50) FOREIGN KEY REFERENCES Suppliers(sid),
pid VARCHAR2(50) FOREIGN KEY REFERENCES Parts(pid),
quantity VARCHAR2(50)
);

Upvotes: 0

Views: 55

Answers (2)

William Robertson
William Robertson

Reputation: 16001

Constraint syntax is documented here.

inline_constraint::= inline_constraint

out_of_line_constraint::= out_of_line_constraint

references_clause::= enter image description here

Your examples are inline, as they are part of the column definition rather than in a separate section or statement.

I would write it as:

create table catalog
( sid       references suppliers(sid)
, pid       references parts(pid)
, quantity  varchar2(50) );

or if I wanted to name them explicitly:

create table catalog
( sid       constraint cat_supplier_fk references suppliers(sid)
, pid       constraint cat_part_fk references parts(pid)
, quantity  varchar2(50) );

You can explicitly override the default datatype if you want, but I consider it better practice to allow it to inherit from the parent. (You can only do this with inline constraints.)

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65433

You need to remove the parts FOREIGN KEY as :

CREATE TABLE Catalog(
sid VARCHAR2(50) REFERENCES Suppliers(sid),
pid VARCHAR2(50) REFERENCES Parts(pid),
quantity VARCHAR2(50)
);

or you may create as :

CREATE TABLE Catalog
( sid VARCHAR2(50),
  pid VARCHAR2(50),
  quantity VARCHAR2(50),
    CONSTRAINT fk_supplier
    FOREIGN KEY (sid)
    REFERENCES suppliers(sid),
    CONSTRAINT fk_parts
    FOREIGN KEY (pid)
    REFERENCES parts(pid)    
);

by defining FOREIGN CONSTRAINTS' names

or if the table already has been created before, by the following way, the constraints may be added later :

CREATE TABLE Catalog
( sid VARCHAR2(50),
  pid VARCHAR2(50),
  quantity VARCHAR2(50)  
);

ALTER TABLE Catalog
ADD CONSTRAINT fk_supplier
  FOREIGN KEY (sid)
  REFERENCES suppliers(sid);

ALTER TABLE Catalog
ADD CONSTRAINT fk_parts
  FOREIGN KEY (pid)
  REFERENCES parts(pid);

Upvotes: 2

Related Questions