Creating Table (Oracle)

Question

I am creating two tables. The first table creates with no errors, but when I try to create the SUBHEAD table, I get an error: Line 2, Missing right parenthesis. I am not sure what is wrong with this line. Below is my SQL statements:

CREATE TABLE HEAD
   (Code NUMERIC(4,0) NOT NULL PRIMARY KEY,
    HeadName VARCHAR(50) NOT NULL UNIQUE,
    HType VARCHAR(1) NOT NULL,
    HDate DATE NOT NULL,
    OpBal DECIMAL(11,2) NOT NULL
   );

CREATE TABLE SUBHEAD
   (HCode NUMERIC(4,0) NOT NULL FOREIGN KEY REFERENCES HEAD(Code),
    SubCode NUMERIC(4,0) NOT NULL,
    SubName VARCHAR(50) NOT NULL,
    SDate DATE NOT NULL,
    OpBal DECIMAL (11,2) NOT NULL,
    CONSTRAINT pk_subheadID PRIMARY KEY (HCode, SubCode)
   );

How comes it doesn't work where I have it? I thought you could do it in both places. — Stc5097
– Stc5097, Commented Apr 6, 2014 at 19:56

evenro · Accepted Answer · 2014-04-06 20:59:04Z

2

syntax: http://docs.oracle.com/cd/B28359_01/server.111/b28286/clauses002.htm#SQLRF52167
note that "in-line" constraint syntax is : "col col_type REFERENCES TAB(COL)" and not "FOREIGN KEY"

The data type for number is NUMBER not NUMERIC

So my memory did not fool me - you can have multiple in-line constraints (although the syntax graph doesn't show it):

SQL> create table tt1(a number primary key);

Table created.

SQL> create table tt2(a number references tt1(a) not null);

Table created.

edited Apr 6, 2014 at 20:59

answered Apr 6, 2014 at 20:38

evenro

2,66422 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

orbfish · Accepted Answer · 2014-04-06 21:04:31Z

2

CREATE TABLE SUBHEAD
   (HCode NUMERIC(4,0) NOT NULL, FOREIGN KEY (Hcode) REFERENCES HEAD(Code),
    SubCode NUMERIC(4,0) NOT NULL,
    SubName VARCHAR(50) NOT NULL,
    SDate DATE NOT NULL,
    OpBal DECIMAL (11,2) NOT NULL,
    CONSTRAINT pk_subheadID PRIMARY KEY (HCode, SubCode)
   );

(note comma, and reference to the new table column)

answered Apr 6, 2014 at 21:04

orbfish

7,78115 gold badges65 silver badges80 bronze badges

Collectives™ on Stack Overflow

Creating Table (Oracle)

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related