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