Solved

SQL: Issues with creating tables

Posted on 2014-04-29
16
719 Views
Last Modified: 2014-04-29
Okay so I have a long script that is being used to insert a bunch of tables and data. However there are three locations that it's getting hung up on.  4 of the other tables were created just fine..but for some reason these particular ones are having issues. The last one states there is a invalid identifier? Where would that be at? The other two, I'm assuming is a issue with the foreign keys. The tables have two foreign keys which is different compared to the other tables, I'm guessing the syntax is wrong? I know how to do it with one foreign key, is there a better way to state two foreign keys? Here are the errors below:

CREATE TABLE ORDERS(
ORDER_ID NUMBER PRIMARY KEY,
ORDER_DATE DATE NOT NULL,
ORDER_QUANTITY NUMBER(10) NOT NULL,
FOREIGN KEY (BID_ID) REFERENCES BID (BID_ID),
ON DELETE CASCADE,
FOREIGN KEY (BILL_ID) REFERENCES BILL (BILL_ID),
ON DELETE CASCADE
);


ORA-00904: : invalid identifier
--------------------------------------------------------------------------------

CREATE TABLE PRODUCTS(
PROD_ID NUMBER PRIMARY KEY,
PROD_NAME DATE VARCHAR(20) NOT NULL,
PROD_COST NUMBER(20) NOT NULL,
FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID),
ON DELETE CASCADE,
FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER (SUPPLIER_ID),
ON DELETE CASCADE
);


ORA-00907: missing right parenthesis
--------------------------------------------------------------------------------

CREATE TABLE BILLING(
BILL_ID NUMBER PRIMARY KEY,
BILL_DUE DATE DATE NOT NULL,
BILL_DISCOUNT VARCHAR(20) NOT NULL,
BILL_TERM VARCHAR(20) NOT NULL
);


ORA-00907: missing right parenthesis

Open in new window

0
Comment
Question by:Pancake_Effect
  • 5
  • 5
  • 3
  • +2
16 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40029740
Commas before ON DELETE CASCADE.

Change:
FOREIGN KEY (BID_ID) REFERENCES BID (BID_ID),
ON DELETE CASCADE,

to:
FOREIGN KEY (BID_ID) REFERENCES BID (BID_ID)
ON DELETE CASCADE,


Same for the other constraint.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40029747
Also:
PROD_NAME DATE VARCHAR(20) NOT NULL,

VARCHAR2 not VARCHAR
0
 
LVL 14

Expert Comment

by:Zac Harris
ID: 40029765
The primary cause of the error ORA-00904 is when you try to execute a SQL statement that includes an invalid column name or the column name is missing. This commonly occurs when you reference an invalid alias in a SELECT statement.

I would reference this message board article to help resolve that issue: Link to Article

The ORA-00907 errors are pretty easy to understand. All parenthesis must be entered in pairs (i.e. an open and close parenthesis.) Double check your statements to ensure that all Parenthesis entries have both an open "(" parenthesis and a close ")" parenthesis.
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40029822
Thanks guys, I also saw I had a couple extra "dates" in there. I also took the extra commas out of the constraint statements as slight mentioned. The BILLING table is now working. However I'm still having the issues with the two tables that have the foreign keys:

Updated Code:

CREATE TABLE ORDERS(
ORDER_ID NUMBER PRIMARY KEY,
ORDER_DATE DATE NOT NULL,
ORDER_QUANTITY NUMBER(10) NOT NULL,
FOREIGN KEY (BID_ID) REFERENCES BID (BID_ID)
ON DELETE CASCADE,
FOREIGN KEY (BILL_ID) REFERENCES BILL (BILL_ID),
ON DELETE CASCADE
);


ORA-00904: : invalid identifier
--------------------------------------------------------------------------------

CREATE TABLE PRODUCTS(
PROD_ID NUMBER PRIMARY KEY,
PROD_NAME VARCHAR(20) NOT NULL,
PROD_COST NUMBER(20) NOT NULL,
FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID)
ON DELETE CASCADE,
FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER (SUPPLIER_ID)
ON DELETE CASCADE
);


ORA-00904: "ORDER_ID": invalid identifier

Open in new window


@itguy012006

Thanks for the article and info. To me it looks like all of the brackets are where they need to be. It works for all the other tables...except these tables have two foreign keys..I imagine the culprit is within there somewhere, but I can't seem to figure out why. I do need two foreign keys though.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40029833
See my first post.  You still have an extra comma:
FOREIGN KEY (BILL_ID) REFERENCES BILL (BILL_ID),
ON DELETE CASCADE


Needs to be:
FOREIGN KEY (BILL_ID) REFERENCES BILL (BILL_ID)
ON DELETE CASCADE



On:
FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID)
ON DELETE CASCADE,


There is no ORDER_ID in the table so how can it be a foreign key?
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40029891
In addition, in ORDERS table, you are creating foreign keys on BID_ID and BILL_ID, but those columns do not exist in the table.
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40029922
Are you saying no commas then on the constraint? But why do you put a comma on the second constraint as shown in your example? Sorry I'm kind of confused there.. :(

In regards to ORDER_ID, it does exist, it's created in the first table:

"ORDER_ID NUMBER PRIMARY KEY,"

The second table just references it as a foreign key.


CREATE TABLE ORDERS(
ORDER_ID NUMBER PRIMARY KEY,
ORDER_DATE DATE NOT NULL,
ORDER_QUANTITY NUMBER(10) NOT NULL,
FOREIGN KEY (BID_ID) REFERENCES BID (BID_ID)
ON DELETE CASCADE
FOREIGN KEY (BILL_ID) REFERENCES BILL (BILL_ID),
ON DELETE CASCADE
);
CREATE TABLE PRODUCTS(
PROD_ID NUMBER PRIMARY KEY,
PROD_NAME VARCHAR(20) NOT NULL,
PROD_COST NUMBER(20) NOT NULL,
FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID)
ON DELETE CASCADE
FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER (SUPPLIER_ID)
ON DELETE CASCADE
);
CREATE TABLE BILLING(
BILL_ID NUMBER PRIMARY KEY,
BILL_DUE DATE NOT NULL,
BILL_DISCOUNT VARCHAR(20) NOT NULL,
BILL_TERM VARCHAR(20) NOT NULL
)

Open in new window



NOTE* BID_ID table creation is just not listed in the code, you do see it referenced though. (left it out because that table was created just fine.) As I mentioned origionally there is actually about 6 tables in the complete script. These are the only ones causing issues.
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40029936
If it makes things easier and to avoid confusion, here is the entire script, but again only the above mentioned ones are causing issues. (The ORDERS and PRODUCTS tables)

CREATE TABLE CUSTOMER(
CUST_ID NUMBER PRIMARY KEY,
CUST_LNAME VARCHAR(20) NOT NULL,
CUST_FNAME VARCHAR(15) NOT NULL,
CUST_ADDRESS VARCHAR(35) NOT NULL,
CUST_PHONE CHAR(10) NOT NULL,
CUST_EMAIL VARCHAR(35),
CUST_BAL NUMBER(5,2) DEFAULT 0.00
);
CREATE TABLE BID(
BID_ID NUMBER PRIMARY KEY,
CUST_ID NUMBER,
BID_MAN_HOURS NUMBER(4,0)  NOT NULL,
BID_MATERIALS VARCHAR(1000)NOT NULL,
BID_OVERALL_PRICE FLOAT(10) NOT NULL,
BID_SENT DATE,
FOREIGN KEY (CUST_ID) REFERENCES CUSTOMER (CUST_ID)
ON DELETE CASCADE
);
CREATE TABLE ORDERS(
ORDER_ID NUMBER PRIMARY KEY,
ORDER_DATE DATE NOT NULL,
ORDER_QUANTITY NUMBER(10) NOT NULL,
FOREIGN KEY (BID_ID) REFERENCES BID (BID_ID)
ON DELETE CASCADE
FOREIGN KEY (BILL_ID) REFERENCES BILL (BILL_ID)
ON DELETE CASCADE
);
CREATE TABLE PRODUCTS(
PROD_ID NUMBER PRIMARY KEY,
PROD_NAME VARCHAR(20) NOT NULL,
PROD_COST NUMBER(20) NOT NULL,
FOREIGN KEY (ORDER_ID) REFERENCES ORDERS (ORDER_ID)
ON DELETE CASCADE
FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER (SUPPLIER_ID)
ON DELETE CASCADE
);
CREATE TABLE BILLING(
BILL_ID NUMBER PRIMARY KEY,
BILL_DUE DATE NOT NULL,
BILL_DISCOUNT VARCHAR(20) NOT NULL,
BILL_TERM VARCHAR(20) NOT NULL
);
CREATE TABLE SUPPLIER(
SUPPLIER_ID NUMBER PRIMARY KEY,
SUPPLIER_EMAIL VARCHAR(20),
SUPPLIER_PHONE CHAR(10) NOT NULL,
SUPPLIER_ADDRESS VARCHAR(20) NOT NULL,
SUPPLIER_CONTACT_LNAME VARCHAR(20) NOT NULL,
SUPPLIER_CONTACT_FNAME VARCHAR(20) NOT NULL,
SUPPLIER_NAME VARCHAR(20) NOT NULL,
SUPPLIER_BAL NUMBER(20) NOT NULL,
SUPPLIER_DUE_DATE DATE
)

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 250 total points
ID: 40029989
Constraints need to be on a column already in the table.  The column needs to exist in both tables.  So, it should look like this:

CREATE TABLE ORDERS(
ORDER_ID NUMBER PRIMARY KEY,
BID_ID NUMBER,
BILL_ID NUMBER,
ORDER_DATE DATE NOT NULL,
ORDER_QUANTITY NUMBER(10) NOT NULL,
FOREIGN KEY (BID_ID) REFERENCES BID (BID_ID)
ON DELETE CASCADE
FOREIGN KEY (BILL_ID) REFERENCES BILL (BILL_ID)
ON DELETE CASCADE
);

Open in new window


Also, in your script, the BILLING table needs to be created before the ORDERS table.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40030018
>>Are you saying no commas then on the constraint?

Comma's separate pieces of the statement.  You have a comma in the middle of defining the foreign key constraint.

So:
FOREIGN KEY (BILL_ID) REFERENCES BILL (BILL_ID), ON DELETE CASCADE

Should be:
FOREIGN KEY (BILL_ID) REFERENCES BILL (BILL_ID) ON DELETE CASCADE


To add to johnsone's post on constraints:
The pseudo text description reads like this:
create a foreign key and (A_COLUMN_OF_THIS_TABLE) references ANOTHER_TABLE(A_COLUMN_IN_THAT_TABLE).
0
 
LVL 4

Author Comment

by:Pancake_Effect
ID: 40030042
Ah gotcha! Thanks, I will update the rest of my tables with that. And that's nice to know that the order matters. I thought that the entire script is compiled at once so it does not matter, but apparently it does, I'll rearrange it.

However, I noticed the script is still having issues. Using your example above:

CREATE TABLE ORDERS(
ORDER_ID NUMBER PRIMARY KEY,
BID_ID NUMBER,
BILL_ID NUMBER,
ORDER_DATE DATE NOT NULL,
ORDER_QUANTITY NUMBER(10) NOT NULL,
FOREIGN KEY (BID_ID) REFERENCES BID (BID_ID)
ON DELETE CASCADE
FOREIGN KEY (BILL_ID) REFERENCES BILL (BILL_ID)
ON DELETE CASCADE
);

Open in new window


I'm still getting the following error:

"ORA-00907: missing right parenthesis"

I don't know where the bracket could be missing, it looks okay to me anyways in regards to the parenthesis.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 40030049
OK, let's try it this way:
comma's separate units of work.

It goes like:
column clause,
column clause,
constraint clause,
constraint  clause

Each column and constraint with ALL parameters for each (data type, constraint action, etc...) is a single unit of work.


Since you have two foreign keys you need a comma between them:
FOREIGN KEY (BID_ID) REFERENCES BID (BID_ID)
ON DELETE CASCADE, --comma needed here
FOREIGN KEY (BILL_ID) REFERENCES BILL (BILL_ID) --comma invalid here
ON DELETE CASCADE


The on delete cascade was part of the constraint so it didn't need a comma before it.


>>I thought that the entire script is compiled at once so it does not matter, but apparently it does, I'll rearrange it.

The ';' is a command terminator.  The file the commands are placed in is just a series of individual commands.  Like any other scripting language:  Statements are executed top to bottom as they are read.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 40030087
CREATE TABLE statements are DDL and have an implied commit.  Each statement is its own transaction.  Therefore,  you need to create the table that is being referred to before you can create the referential integrity constraints.

The best way around this limitation is to create all the tables, then use ALTER TABLE commands to create all the constraints.  There really is no difference to doing it this way.

I believe you can get around the ordering of the tables and constraints if you use a CREATE SCHEMA command, however that is probably more complexity than you need.
0
 
LVL 4

Author Closing Comment

by:Pancake_Effect
ID: 40030157
Thank you everyone, I think I finally got it working! I learned a lot about code order, foreign keys, and commas haha (thanks slight & johnsone you made it really easy to understand). Slowly I'm becoming less of a noob, but hopefully I'll grasp it more little by little. Thanks again.
0
 
LVL 31

Expert Comment

by:awking00
ID: 40030162
Now that the commas seem to be resolved, I think there are still issues -
In your create orders table statement, you say REFERENCES BILL (BILL_ID)
when the table created with the BILL_ID was BILLING.
Without an orders table created, you can't reference it when creating the
products table.
Additionally, the create products table statement references the supplier
table before the supplier table gets created. I think the order needs to
be changed as in the attached file.
create-tables.txt
0
 
LVL 31

Expert Comment

by:awking00
ID: 40030174
Wow. Sorry it took me so long to type my response :-(
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now