Re: hi all

Kusuma Pabba, 17.02.2009 13:54:
> while in pgsql i am thinking of to use the same as follows:
>
> CREATE TABLE users (
>  user_id int(11) NOT NULL serial,
>  user_name varchar(50) NOT NULL,
>  first_name varchar(50) default NULL,
>  middle_name varchar(50) default NULL,
>  last_name varchar(50) default NULL,
>  password varchar(50) default NULL,
>  salt varchar(50) default NULL,
>  secret_question varchar(255) default NULL,
>  secret_answer varchar(255) default NULL,
>  creator int(11) default NULL,
>  date_created datetime NOT NULL default '0000-00-00 00:00:00',
>  changed_by int(11) default NULL,
>  date_changed datetime default NULL,
>  voided smallint(1) NOT NULL default '0',
>  voided_by int(11) default NULL,
>  date_voided datetime default NULL,
>  void_reason varchar(255) default NULL,
>  PRIMARY KEY  (user_id),
>  KEY users_user_creator (creator),
>  KEY users_user_who_changed_user (changed_by),
>  KEY users_user_who_voided_user (voided_by),
>  CONSTRAINT users_user_creator FOREIGN KEY (creator) REFERENCES users
> (user_id),
>  CONSTRAINT users_user_who_changed_user FOREIGN KEY (changed_by)
> REFERENCES users (user_id),
>  CONSTRAINT users_user_who_voided_user FOREIGN KEY (voided_by)
> REFERENCES users (user_id)
> ) ;
>
> will that be valid to create a table like this
> if no what all have to  be replaced
> thanks for any help
>

0000-00-00 00:00:00 is not a valid date and will (rightfully) be rejected by Postgres
(I would also recommend not to use use it in MySQL either).

If you can't supply a date use a NULL value.

Btw: why do you use varchar(255).
When I see 255, I always suspect people are assuming some kind of limits that just aren't there.
Do you have a business constraint that requires exactly 255 (as opposed to 300, 500 or 250)?

Thomas