Re: parse error creating table

[Ferindo Middleton Jr <suppressed>]

Thanks Peter. I did originally use this table in a Postgresql db. Thanks 
for you advice. Your suggestions below allowed me to create this table 
and I learned a thing t two about proper usage of the TIMESTAMP data 
type. The intended effect is to get a timestamp field that inserts the 
current system time on inserts and continues to update the field with 
the current timestamp on updates without the application or use needing 
to specify it.... are you saying that the timestamp attribute alone will 
do that?

Ferindo

Peter Brawley wrote:
> 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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/suppressed