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
>>
>
>
