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

Hi Ferindo

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

Yep. Try...

create table ts(i int,ts timestamp);
insert into ts values(1,null);
select * from ts;
+------+---------------------+
| i    | ts                  |
+------+---------------------+
|    1 | 2005-12-04 17:21:01 |
+------+---------------------+
update ts set i=2;
select * from ts;
+------+---------------------+
| i    | ts                  |
+------+---------------------+
|    2 | 2005-12-04 17:21:13 |
+------+---------------------+

PB
http://www.artfulsoftware.com

-----


Ferindo Middleton Jr wrote:

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


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