CODEWITHSUNDEEP
sqloracle-database
Stc5097
Stc5097

Reputation: 289

Error creating table in oracle

I am trying to create a table in oracle but I am getting this error: unknown command ")" - rest of line ignored. I can't figure out what is causing this error. Below is my SQL for the table: 

CREATE TABLE PAYMENT
   (PayNum INT NOT NULL PRIMARY KEY,
    CType VARCHAR(1) NOT NULL,
    CCNum VARCHAR(16) NOT NULL,
    BankName VARCHAR(75) NOT NULL,
    AccNum INT NOT NULL,
    PDate DATE NOT NULL,
    Amt DECIMAL(11,2) NOT NULL,
    CONSTRAINT fk_BANKACC_PAYMENT FOREIGN KEY (BankName, AccNum)
    REFERENCES BANKACC(BankName, AccNum),
    CONSTRAINT fk_CRCARD_PAYMENT FOREIGN KEY (CType, CCNum)
    REFERENCES CRCARD(CType, CCNum)

);

Upvotes: 0

Views: 560

Answers (3)

Alex Poole
Alex Poole

Reputation: 191560

If you're running this in SQL*Plus, get rid of the blank line between REFERENCES and ):

    REFERENCES CRCARD(CType, CCNum)
);

Or set sqlblanklines on to change the behaviour.

By default it interprets a blank line as the end of the statement, but doesn't run it. So your entire CREATE TABLE command is essentially ignored, and the ); is treated as a stanalone command. Hence the error message you get, since it doesn't mean anything on its own.

Upvotes: 2

Ajay Gupta
Ajay Gupta

Reputation: 514

Your code is correct. Make sure you are referencing primary keys (all 4). Check this: http://sqlfiddle.com/#!2/7be70/1/0

If you do not follow that, you may get this error: There are no primary or candidate keys in the referenced table 'BANKACC' that match the referencing column list in the foreign key 'fk_BANKACC_PAYMENT'. Code in the fiddle above:

CREATE TABLE BANKACC
(BankName VARCHAR(75) NOT NULL,
    AccNum INT NOT NULL,
PRIMARY KEY(BankName, AccNum));

CREATE TABLE CRCARD
(CType VARCHAR(1) NOT NULL,
    CCNum VARCHAR(16) NOT NULL,
PRIMARY KEY(CType, CCNum));

CREATE TABLE PAYMENT
   (PayNum INT NOT NULL PRIMARY KEY,
    CType VARCHAR(1) NOT NULL,
    CCNum VARCHAR(16) NOT NULL,
    BankName VARCHAR(75) NOT NULL,
    AccNum INT NOT NULL,
    PDate DATE NOT NULL,
    Amt DECIMAL(11,2) NOT NULL,
    CONSTRAINT fk_BANKACC_PAYMENT FOREIGN KEY (BankName, AccNum)
    REFERENCES BANKACC(BankName, AccNum),
    CONSTRAINT fk_CRCARD_PAYMENT FOREIGN KEY (CType, CCNum)
    REFERENCES CRCARD(CType, CCNum)

);

Also you should read this for better understanding on how to implement foreign key constraint: http://docs.oracle.com/cd/E17952_01/refman-5.5-en/create-table-foreign-keys.html

Upvotes: 2

evenro
evenro

Reputation: 2646

Please use NUMBER for numeric columns.

http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT313

Also, in Oracle, use VARCHAR2 for strings.

but of it your syntax should be correct.

Upvotes: 0

Related Questions