SQL Syntax Error whilst creating a table

Question

I have recently started learning SQL, I can't determine what is causing my syntax error (see below):

CREATE TABLE Users(
    user_id smallint not null auto_increment,
    username varchar(50) unique,
    password varchar(41),
    dob date,
    session_id varchar not null,
    cookie varchar not null,
    PRIMARY KEY(user_id),
    CONSTRAINT dob_valid CHECK(dob < current_date())
);

This does work when all varchar fields have a predefined length. Do all fields of type varchar require a size parameter?

I am sorry if this problem seems very simple.

Thanks for your help in advance

it says what is causing syntax error. in the error message

Your Common Sense
– Your Common Sense

2010-09-11 17:40:57 +00:00
Commented Sep 11, 2010 at 17:40 — Your Common Sense
– Your Common Sense, Commented Sep 11, 2010 at 17:40

Hogan · Accepted Answer · 2010-09-11 17:35:05Z

2

yes all varchar require a max size.

answered Sep 11, 2010 at 17:35

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

binarycreations Over a year ago

ah thank you, is this the same for the char data type. What should you do if you do not know the what the size of this field will be?

binarycreations Over a year ago

answered by own question thanks to alex martelli, a very helpful post!

Alex Martelli · Accepted Answer · 2010-09-11 17:44:07Z

As the docs say,

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store.

Another way of checking this (that the length is not optional): check the data_type production in the relevant docs's page on syntax (for CREATE TABLE) and you'll see, in part:

 VARCHAR(length)
      [CHARACTER SET charset_name] [COLLATE collation_name]

See? the (length) part is not within square brackets in this syntax description: that means it's not optional, but mandatory (the CHARACTER SET and COLLATE parts are optional, so they're within square brackets).

I'm explaining this technique for reading the docs at length, because, while learning the specific syntax for this very specific use case is of course important, learning to read the manuals and find answers to your questions (syntax and otherwise) in a very speedy manner is even more important, and will increase your SQL productivity enormously.

Collectives™ on Stack Overflow

SQL Syntax Error whilst creating a table

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related