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
