Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 766
  • Last Modified:

SQL: Issues with creating tables

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
Pancake_Effect
Asked:
Pancake_Effect
  • 5
  • 5
  • 3
  • +2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
Also:
PROD_NAME DATE VARCHAR(20) NOT NULL,

VARCHAR2 not VARCHAR
0
 
Zac HarrisCommented:
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
Pancake_EffectAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
Pancake_EffectAuthor Commented:
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
 
Pancake_EffectAuthor Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
Pancake_EffectAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
Pancake_EffectAuthor Commented:
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
 
awking00Commented:
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
 
awking00Commented:
Wow. Sorry it took me so long to type my response :-(
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now