What’s Wrong with this Create Table Statement?

thatjeffsmith General 10 Comments

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:

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.

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

Comments 10

  1. I am a student who is barely learning about scripting for I am going for my associates in database development. I am required to develop a mock database in which I create tables and etc., but with the examples the book provides it suggest to use Char not VarChar but I was able to play around (because there is no SQL Developer Data Modeler to assist) and I got it to work. Obviously it still needs a lot of work again this only to give me an idea on how things work.

  2. Jeff – not bad for a non- data modeler type product manager 😉

    But you missed the most important point – if they had created the design using SQL Developer Data Modeler then there would have been zero errors in the DDL generated. Even a beginner can’t mess up the syntax using a good data modeling tool (unless of course they generate Oracle syntax and try to run in SQL Server).

    But even the best data modeling tool cannot fix bad design decisions.

    1. thatjeffsmith Post
      Author
  3. There can be good reasons for using CHAR, from a storage perspective fixed length fields are easier to handle. But the flexibility of VARCHAR2 is much greater and is likely a better choice more often then not. And yes, always use VARCHAR2 not VARCHAR.

    1. thatjeffsmith Post
      Author
      1. thatjeffsmith Post
        Author
  4. and help us if the customer wants to send this to another address not their own. or they have multiple phones. or 2 address lines. while i’m at it, why isn’t there a state table? shouldn’t that be FK’d to this one?

    1. thatjeffsmith Post
      Author

Leave a Reply

Your email address will not be published. Required fields are marked *