Re: hi all

Kusuma Pabba wrote:
> when creating tables,
>
> in my sql i have used create table
> CREATE TABLE `users` (
> `user_id` int(11) NOT NULL auto_increment,
> `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` tinyint(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`)
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
>
>
> 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
>
>
> Regards
> kusuma.p
>
CREATE TABLE users (
user_id serial NOT NULL ,
user_name varchar(50) NOT NULL,
first_name varchar(50),
middle_name varchar(50),
last_name varchar(50),
password varchar(50),
salt varchar(50),
secret_question varchar(255),
secret_answer varchar(255),
creator int,
date_created timestamp NOT NULL default to_timestamp('0000-00-00
00:00:00','YYYY-MM-DD HH24:MI:SS'),
changed_by int,
date_changed timestamp,
voided smallint NOT NULL default '0',
voided_by int,
date_voided timestamp,
void_reason varchar(255),
PRIMARY KEY (user_id),
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)
) ;

CREATE INDEX users_user_creator ON users (creator);
CREATE INDEX users_user_who_changed_user ON users (changed_by);
CREATE INDEX users_user_who_voided_user On users (voided_by);

postgres=# \d users
Table
"public.users"
Column | Type
| Modifiers
-----------------+-----------------------------+-------------------------------------------------------------------------------------------
user_id | integer | not null default
nextval('users_user_id_seq'::regclass)
user_name | character varying(50) | not null
first_name | character varying(50) |
middle_name | character varying(50) |
last_name | character varying(50) |
password | character varying(50) |
salt | character varying(50) |
secret_question | character varying(255) |
secret_answer | character varying(255) |
creator | integer |
date_created | timestamp without time zone | not null default
to_timestamp('0000-00-00 00:00:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)
changed_by | integer |
date_changed | timestamp without time zone |
voided | smallint | not null default
0::smallint
voided_by | integer |
date_voided | timestamp without time zone |
void_reason | character varying(255) |
Indexes:
"users_pkey" PRIMARY KEY, btree (user_id)
"users_user_creator" btree (creator)
"users_user_who_changed_user" btree (changed_by)
"users_user_who_voided_user" btree (voided_by)
Foreign-key constraints:
"users_user_creator" FOREIGN KEY (creator) REFERENCES users(user_id)
"users_user_who_changed_user" FOREIGN KEY (changed_by) REFERENCES
users(user_id)
"users_user_who_voided_user" FOREIGN KEY (voided_by) REFERENCES
users(user_id)

postgres=# insert into users (user_name) values ('foo');
INSERT 0 1
postgres=# select * from users;
user_id | user_name | first_name | middle_name | last_name | password |
salt | secret_question | secret_answer | creator |
date_created | changed_by | date_changed | voided | voided_by |
date_voided | void_reason
---------+-----------+------------+-------------+-----------+----------+------+-----------------+---------------+---------+------------------------+------------+--------------+--------+-----------+-------------+-------------
1 | foo | | | |
| | | | | 0001-01-01 00:00:00
BC | | | 0 | | |
(1 row)

postgres=#