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=#
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
