0

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)

);
2
  • 4
    What client are you using to run this statement? Some clients (eg SQL*Plus) don't like blank lines and you have a blank line before the closing bracket. Commented Apr 7, 2014 at 6:44
  • This is most likly the problem and Alex Poole's answer addresses this. Commented Apr 7, 2014 at 7:41

3 Answers 3

2

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

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

Comments

2

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.

Comments

0

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.

1 Comment

INT is a synonym for NUMBER. And there is nothing wrong with VARCHAR. it's a synonym for VARCHAR2.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.