From: Peter Brawley
Date: December 4 2005 10:41pm
Subject: Re: parse error creating table
List-Archive: http://lists.mysql.com/mysql/192502
Message-Id: <439370B3.70504@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Ferindo

One problem is:
   employment_status_id  INTEGER REFERENCES employment_statuses(id) NOT 
NULL,
NOT NULL should be before REFERENCES.

Also, in:
   last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
  (i) NOT NULL is superfluous since the default is given by 
CURRENT_TIMESTAMP.
  (ii) specifying DEFAULT CURRENT_TIMESTAMP defeats auto-resetting of 
the timestamp on updates. Is
 that what you want? To get auto-setting on INSERTs and UPDATEs, just write
  last_updated TIMESTAMP,
Also the manual doesn't mention TIME WITHOUT TIME ZONE. Are you thinking 
of PostgreSQL?

PB

-----

Ferindo Middleton Jr wrote:

> I have been trying to create a table but mysql 5.0.15-nt-max is having 
> a problem parsing the statement. Anyone know what the problem is in 
> the syntax of the following table creation statement:
>
> CREATE TABLE registration_and_attendance (
>                         id                    SERIAL NOT NULL UNIQUE,
>                         firstname        VARCHAR(256) NOT NULL,
>                         middlename    TEXT,
>                         lastname        VARCHAR(256),
>                         suffix              TEXT,
>                         sf182_received    BOOLEAN NOT NULL DEFAULT TRUE,
>                         registrant_email_address   TEXT,
>                         cc_email_list   TEXT,
>                         bureau_id          INTEGER REFERENCES 
> bureaus(id),
>                         office              TEXT,
>                         class_id          INTEGER NOT NULL REFERENCES 
> classes(id),
>                         schedule_id      INTEGER REFERENCES 
> schedules(id),
>                         start_date      DATE,
>                         end_date       DATE,
>                         enrolled          BOOLEAN,
>                         attended        BOOLEAN,
>                         completed      BOOLEAN,
>                         cancelled        BOOLEAN DEFAULT FALSE,
>                         cancelled_comments    TEXT,
>                         comments       TEXT,
>                         email_confirmation_sent      BOOLEAN NOT 
> NULL,   employment_status_id  INTEGER REFERENCES 
> employment_statuses(id) NOT NULL,
>     last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
>     last_user_updated   TEXT,
>                         waitlisted     BOOLEAN DEFAULT FALSE,
>                         overflow_registrant    BOOLEAN DEFAULT FALSE,
>                         attach_hotel_listing_directions BOOLEAN,
>                         instructor_legacy TEXT,
>                         time_legacy TIME WITHOUT TIME ZONE,
>                         ssn_legacy   TEXT,
>                         position_grade_title     TEXT,
>                         office_phone_legacy      TEXT,
>                         contractor_legacy       BOOLEAN,
>                         no_show_legacy         BOOLEAN,
>                         status_legacy         TEXT,
>                    funding_id      INTEGER REFERENCES funding_types(id),
>          PRIMARY KEY (firstname, lastname, class_id, start_date, 
> end_date)
> );
>
> I get the following error message with the above statement but I can't 
> figure out what the problem is:
>
> ERROR 1064 (42000): You have an error in your SQL syntax; check the 
> manual that
> corresponds to your MySQL server version for the right syntax to use 
> near 'NOT N
> ULL,
>     last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
>     l' at line 23
>
>
> Thanks, Ferindo
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.11/191 - Release Date: 12/2/2005