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.
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.
and…
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.
10 Comments
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.
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.
I was just trying to get people started, there were plenty of problems to point out 🙂
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.
Maybe Ric, but I can think of more reasons not to use them than to use them, esp for those kinds of fields. Of course, always & never, yada yada yada 🙂
Yeah, lots of issues here. Definitely has book example or homework written all over it.
And email addresses can be 254 characters long.
This developer isn’t working in Oregon by any chance?
hehehe, no
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?
Normalization is apparently a 201 course level topic 🙂