ThatJeffSmith

What’s Wrong with this Create Table Statement?

I recently got this request for help via a YouTube video comment. I’m sharing it here, because I see this type of question A LOT. And it scares me.

“When I run this script it reads =
Error starting at line : 1 in command –

What am I doing wrong?

Before I show the code, let’s get this out of the way. I’m happy to help people. But as a ‘tools guy,’ I shouldn’t be confused with a data modeling expert or someone who is fluent in SQL or PL/SQL. However, that doesn’t stop me from answering questions :)

So let’s have a look:

CREATE TABLE A_CUSTOMER (
CUSTOMER_NUMBER INTEGER NOT NULL,
LAST_NAME CHAR (20) NOT NULL,
FIRST_NAME CHAR (20) NOT NULL,
ADDRESS VARCHAR (30) NOT NULL,
CITY CHAR (15) NOT NULL,
STATE CHAR (2) NOT NULL,
ZIP_CODE INTEGER (5) NOT NULL,
PHONE_NUMBER INTEGER (10) NOT NULL,
FAX_NUMBER INTEGER (10) ,
EMAIL_ADDRESS VARCHAR (50) ,
PRIMARY_KEY (CUSTOMER_NUMBER)
)

I’m lazy, so instead of reading the code, I paste it into Oracle SQL Developer v4.0.1 and see what happens.

The first of many issues...

The first of many issues…

Well the tool is telling us exactly where the database engine is tripping over the requested DDL. Both the script output and the text editor are painting the problem spot.

Notice the squiggly red line?

Notice the squiggly red line?

and…

The exact line number and curpos for when the database engine choked

The exact line number and curpos for when the database engine choked

So, INTEGER (5) – that’s an easy ‘fix’ – use INTEGER instead. And that cascades down to the other 2 columns.

So hopefully the person who wrote this code understands INTEGER and just got clumsy – I do this all the time myself.

But there are MUCH bigger problems here

Don’t use CHAR to store things like names, cities, etc. And the lengths are woefully inadequate. Go bigger, and use VARCHAR2 so we don’t have wasted space. Maybe, just maybe I’m helping someone with their homework assignment and the teacher was trying to see if the student noticed the syntax AND design issues. If so, enjoy your A+ :)

There’s another problem here, do you see it?

Zipcode, phone, and fax are both being stored as INTEGERS. What happens if you live in New Hampshire and your zipcode is ‘03110’? If you store this as an integer, your new zipcode is 3110. I imagine that might cause problems for your customers. Same thing with phone numbers, if your customer is outside the United States (where I live), it’s going to start with 011 probably..depending on your frame of reference.

The last problem is with how the primary key is defined. But that’s an easy fix. The not-so-easy fix are the design issues around how you’re storing the customer information. Spend WAY more time with the design, and don’t worry about the SQL syntax up front. That is much easier to fix.

Oh, there’s just one more thing…


I just noticed the VARCHARs. Don’t do that. Use VARCHAR2.