CODEWITHSUNDEEP
sqloracle-databasecreate-table
Stc5097
Stc5097

Reputation: 289

Creating Table (Oracle)

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)
   );

Upvotes: 1

Views: 672

Answers (2)

orbfish
orbfish

Reputation: 7741

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)

Upvotes: 2

evenro
evenro

Reputation: 2646

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.

Upvotes: 2

Related Questions