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