SQL Syntax Error whilst creating a table

Question

Hi,

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

Answer 1

yes all varchar require a max size.

Answer 2

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.