What’s Wrong with this Create Table Statement?

Tell Others About This Story:

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:

  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)      ,

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?


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.

Related Posts Plugin for WordPress, Blogger...
Tell Others About This Story:

Similar Posts by Content Area: ,