CODEWITHSUNDEEP
oracle-database
the_no_1
the_no_1

Reputation: 25

missing right parenthesis SQL

I am new to SQL.

I am trying to create a table:

CREATE TABLE account
(AccountNo NUMBER(2) PRIMARY KEY,
AccountType VARCHAR(1) FOREIGN KEY REFERENCES account_type(TypeCode),
CustomerRef NUMBER(2) FOREIGN KEY REFERENCES bank_customer(CustomerRef),
DateOpened DATE,
CurrentBalence NUMBER(6,2),
OverdraftLimit NUMBER(5,2));

However it comes up with: ORA-00907: missing right parenthesis I know for a fact that you can have to foreign keys, so that's not the problem. Could someone give me a had with the solution of creating the table?

Upvotes: 0

Views: 136

Answers (4)

Multisync
Multisync

Reputation: 8797

There are two notations you can use when declaring constraints:

1) inline (a constraint):

CREATE TABLE account
(AccountNo NUMBER(2) PRIMARY KEY,
AccountType VARCHAR(1) REFERENCES account_type(TypeCode),
CustomerRef NUMBER(2) REFERENCES bank_customer(CustomerRef),
DateOpened DATE,
CurrentBalence NUMBER(6,2),
OverdraftLimit NUMBER(5,2));

2) out-of-line

CREATE TABLE account
(
AccountNo NUMBER(2) PRIMARY KEY,
AccountType VARCHAR(1),
CustomerRef NUMBER(2),
DateOpened DATE,
CurrentBalence NUMBER(6,2),
OverdraftLimit NUMBER(5,2),
FOREIGN KEY(AccountType) REFERENCES account_type(TypeCode),
FOREIGN KEY(CustomerRef) REFERENCES account_type(TypeCode)
);

In both cases you can prepend the constraint declaration with CONSTRAINT <name> to give your name to a constraint, otherwise Oracle assigns its own name.

inline notation is applied to a column where the constraint is declared, out-of-line is applied to the table. There slight differences in syntax + some restrictions, for example you cannot declare out-of-line NULL constraint.

You can use inline and out-of-line syntax in CREATE TABLE and ALTER TABLE. Refer to Oracle documentation for more information

Upvotes: 1

Ascalonian
Ascalonian

Reputation: 15193

You need to define the foreign keys with REFERENCES. In the below snippet, replace AccountTypeTable and CustomerRefTable with the correct table names and replace typeColumn and refColumn with the correct column names in those tables these match to.

UPDATE
I added in the correct values from your comment on another answer

CREATE TABLE account (
    AccountNo NUMBER(2) PRIMARY KEY,
    AccountType VARCHAR(1),
    CustomerRef NUMBER(2),
    DateOpened DATE,
    CurrentBalence NUMBER(6,2),
    OverdraftLimit NUMBER(5,2),
    CONSTRAINT account_fk1 FOREIGN KEY (AccountType) REFERENCES Account_Type(TypeCode),
    CONSTRAINT account_fk2 FOREIGN KEY (CustomerRef) REFERENCES Bank_Customer(CustomerRef));

Upvotes: 0

deejay
deejay

Reputation: 575

you are missing foreign key syntax... 

CREATE TABLE account
(AccountNo NUMBER(2) PRIMARY KEY (P_ID),
AccountType VARCHAR(1) FOREIGN KEY (F1_ID) REFERENCES <table_name>(field_name>),
CustomerRef NUMBER(2) FOREIGN KEY (F2_ID) REFERENCES <table_name>(field_name>),
DateOpened DATE,
CurrentBalence NUMBER(6,2),
OverdraftLimit NUMBER(5,2));

Upvotes: 0

Maik Schmidt
Maik Schmidt

Reputation: 58

A foreign key actually has to refer to something. In your case you have to tell Oracle what AccountType and CustomerRef refer to. Usually it looks as follows:

AccountType VARCHAR(1) FOREIGN KEY REFERENCES TABLE_NAME(COLUMN_NAME)

Of course, you have to replace TABLE_NAME and COLUMN_NAME with the name of the table and the name of the column you're referring to.

Upvotes: 1

Related Questions