ORA-00907: missing right parenthesis 3

[cfneadct]

I have a script creating 10 tables


This one and 2 others load fine.

CREATE TABLE INSURANCE_COMPANY
(
COMPANY_ID NUMBER(6) NOT NULL PRIMARY KEY
, COMPANY_NAME VARCHAR2(20) NOT NULL
, STREET_ADDRESS VARCHAR2(30) NOT NULL
, CITY VARCHAR2(15) NOT NULL
, STATE VARCHAR2(2) NOT NULL
, ZIPCODE VARCHAR2(5) NOT NULL
, CONTACT_PHONE VARCHAR2(10) NOT NULL
);


This one and 6 others keep showing ORA-00907: missing right parenthesis.

CREATE TABLE HOUSEHOLD
(
HOUSEHOLD_ID NUMBER(6) NOT NULL PRIMARY KEY
, HOUSEHOLD_NAME VARCHAR2(20) NOT NULL
, STREET_ADDRESS VARCHAR2(30) NOT NULL
, CITY VARCHAR2(15) NOT NULL
, STATE VARCHAR2(2) NOT NULL
, ZIPCODE VARCHAR2(5) NOT NULL
, HOME_PHONE VARCHAR2(10) NOT NULL
, COMPANY_ID NUMBER(6) NOT NULL FOREIGN KEY
REFERENCES INSURANCE_COMPANY(COMPANY_ID)
, POLICY_NUMBER NUMBER(6) NOT NULL
, RESPONSIBLE_PARTY NUMBER(6) NOT NULL FOREIGN KEY
REFERENCES PATIENT(PATIENT_ID)
, HOUSE_DISCOUNT NUMBER(2) DEFAULT 0
);

I am working in Notepad++, there should not be any hidden characters and uploading to an Oracle Apex 11G instance.

I am at a loss for what I am missing. Any pointers?

Thank you,
JF

 

[karluk]

Oracle is objecting to your attempt to define a foreign key in the middle of a column definition. Try moving the foreign key declarations after all the columns have been defined. For example

CREATE TABLE HOUSEHOLD
(
    HOUSEHOLD_ID    NUMBER(6) NOT NULL PRIMARY KEY
,    HOUSEHOLD_NAME    VARCHAR2(20) NOT NULL
,    STREET_ADDRESS    VARCHAR2(30) NOT NULL
,    CITY    VARCHAR2(15) NOT NULL
,    STATE    VARCHAR2(2) NOT NULL
,    ZIPCODE    VARCHAR2(5) NOT NULL
,    HOME_PHONE    VARCHAR2(10) NOT NULL
,    COMPANY_ID NUMBER(6) NOT NULL 
,    POLICY_NUMBER    NUMBER(6) NOT NULL
,    RESPONSIBLE_PARTY    NUMBER(6) NOT NULL 
,    HOUSE_DISCOUNT    NUMBER(2) DEFAULT 0
,FOREIGN KEY (COMPANY_ID)
        REFERENCES INSURANCE_COMPANY(COMPANY_ID)
,FOREIGN KEY (RESPONSIBLE_PARTY)
        REFERENCES PATIENT(PATIENT_ID)
)

 

[SantaMufasa]

CF,

Sometimes, Oracle is not terribly good at explaining obscure points about their syntax. Creating foreign keys might be one of those cases.

There are two different "venues" for creating foreign key constraints:[ul][li]In-Line Foreign Key Specification: In such case, we use only the keyword, "REFERENCES" while specifying the foreign key. (See your code that I modified, below.)[/li][li]Out-of-Line Foreign Key Specification: This is where we create a foreign key specification separately/away from the foreign key column definition, itself. In such case, since the foreign key column is away from our constraint definition, we use the key words, "FOREIGN KEY", to indicate the column in our table for which we are defining a foreign key constraint that points to (i.e., "REFERENCES") a primary-/unique-key column in some other table. (See the example of an out-of-line foreign-key constraint definition that I contrived/added to your orginal table definition.)[/li][/ul]

CREATE TABLE HOUSEHOLD
(
    HOUSEHOLD_ID    NUMBER(6) NOT NULL PRIMARY KEY
,    HOUSEHOLD_NAME    VARCHAR2(20) NOT NULL
,    EMPLOYEE_ID    NUMBER
,    STREET_ADDRESS    VARCHAR2(30) NOT NULL
,    CITY    VARCHAR2(15) NOT NULL
,    STATE    VARCHAR2(2) NOT NULL
,    ZIPCODE    VARCHAR2(5) NOT NULL
,    HOME_PHONE    VARCHAR2(10) NOT NULL
,    COMPANY_ID NUMBER(6) NOT NULL -- FOREIGN KEY (I commented out "FOREIGN KEY" for in-line definition)
        REFERENCES INSURANCE_COMPANY(COMPANY_ID)
,    POLICY_NUMBER    NUMBER(6) NOT NULL
,    RESPONSIBLE_PARTY    NUMBER(6) NOT NULL -- FOREIGN KEY (I commented out "FOREIGN KEY" for in-line definition)
        REFERENCES PATIENT(PATIENT_ID)
,    HOUSE_DISCOUNT    NUMBER(2) DEFAULT 0
,    CONSTRAINT XYZ [b]FOREIGN KEY[/b] (EMPLOYEE_ID)
        REFERENCES S_EMP(ID) -- out-of-line FK definition
);

Table created.

Let us know if this answers your question.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Sorry that I cross-posted on KarlUK's post...He must type faster than I do. <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[cfneadct]

Thanks, that fixed all of them except this table-

CREATE TABLE PATIENT
(
PATIENT_ID NUMBER(6) NOT NULL
, FIRST_NAME VARCHAR2(10) NOT NULL
, LAST_NAME VARCHAR2(15) NOT NULL
, DOB DATE(10) NOT NULL
, RELATIONSHIP VARCHAR2(10) NOT NULL
, HOUSEHOLD_ID NUMBER(6) NOT NULL
, CONSTRAINT PK_PATIENT_ID PRIMARY KEY (PATIENT_ID)
);

My constraint scripts are all in this format-
ALTER TABLE PATIENT CONSTRAINT FK_HOUSEHOLD_ID
FOREIGN KEY(HOUSEHOLD_ID) REFERENCES HOUSEHOLD(HOUSEHOLD_ID)

I am getting ORA-01735: invalid ALTER TABLE option, is there anything that stands out?

Thank you,
JF

 

[Dagon]

You'd need to say "alter table add constraint..."

For Oracle-related work, contact me through Linked-In.

 

[karluk]

It would be to your advantage to buy a basic Oracle reference book so that you can check the proper syntax of the commands that are generating errors.

The table create is failing because "DATE(10)" is an invalid data type. You can't qualify a date with a length - all Oracle dates have the same length. So you DOB column should be defined as "DOB DATE NOT NULL".

The alter table statements are failing because you failed to use the key word "add". As Dagon says, they should work if you change them to read "alter table add constraint..."