Обсуждение: hi all
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
just outside of your question, you should read about normalization. it is in general very bad idea to have a table that holds all information possible .
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=#
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
a few further comments:
On Tue, Feb 17, 2009 at 06:54:53PM +0530, Ashish Karalkar wrote:
> CREATE TABLE users (
> user_id serial NOT NULL ,
It's common to combine this with the PRIMARY KEY constraint from below
to be:
user_id SERIAL PRIMARY KEY,
the NOT NULL check is implicit in this and thus redundant.
> user_name varchar(50) NOT NULL,
As a general design question; should user_name have a UNIQUE constraint
on it? i.e.
user_name VARCHAR(50) NOT NULL UNIQUE,
> secret_question varchar(255),
> secret_answer varchar(255),
as pointed out, these look like they should probably be of TEXT type.
> creator int,
I'd combine this with the FOREIGN KEY constraint from below as well:
creator INT REFERENCES users (user_id),
> date_created timestamp NOT NULL default to_timestamp('0000-00-00 00:00:00','YYYY-MM-DD HH24:MI:SS'),
What's this strange 0000-00-00 date you speak of? As far as I know
it's not valid; dates go from 1BC to 1AD without a zero in the middle.
Shouldn't you just remove the NOT NULL check or maybe '-infinity' would
be better.
date_created TIMESTAMP,
or
date_created TIMESTAMP NOT NULL DEFAULT '-infinity',
or should it really be
date_created TIMESTAMP NOT NULL DEFAULT now(),
> voided smallint NOT NULL default '0',
Is this really an INT, or should it be a BOOL:
voided BOOL NOT NULL DEFAULT FALSE,
> date_voided timestamp,
I tend to have these as "end dates" defaulting to 'infinity' as it's
easier to do checks on them then:
date_voided TIMESTAMP NOT NULL DEFAULT 'infinity',
hope that helps!
--
Sam http://samason.me.uk/
On Tue, Feb 17, 2009 at 04:40:58PM +0000, Sam Mason wrote:
> > user_name varchar(50) NOT NULL,
>
> As a general design question; should user_name have a UNIQUE
> constraint on it? i.e.
>
> user_name VARCHAR(50) NOT NULL UNIQUE,
Yes, it's good to have a UNIQUE constraint, but not this one. To have
a sane one, it needs further constraints, and in 8.4, case-insensitive
text (citext) type. Here's one that is reasonably sane until citext
is available.
user_name TEXT, -- unless length is an integrity constraint, use TEXT instead of VARCHAR.
then later:
CREATE UNIQUE INDEX unique_user_name_your_table
ON your_table(LOWER(TRIM(user_name)))
You might also require that whitespace be treated in some consistent
way, one example of which is simply forbidding whitespace in user_name
at all. This you can do via CHECK constraints or a DOMAIN.
> creator INT REFERENCES users (user_id),
>
> > date_created timestamp NOT NULL default to_timestamp('0000-00-00 00:00:00','YYYY-MM-DD HH24:MI:SS'),
>
> What's this strange 0000-00-00 date you speak of? As far as I know
> it's not valid; dates go from 1BC to 1AD without a zero in the middle.
> Shouldn't you just remove the NOT NULL check or maybe '-infinity' would
> be better.
Either require a created_date and make the default
sane--CURRENT_TIMESTAMP, e.g.--or don't require one, but making a
nonsense date is Bad(TM).
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Tue, Feb 17, 2009 at 5:54 AM, Kusuma Pabba <kusumap@ncoretech.com> wrote: > while in pgsql i am thinking of to use the same as follows: > > CREATE TABLE users ( > user_id int(11) NOT NULL serial, I see this construct in mysql all the time. Funny thing is most mysql users think it means an 11 character wide int, i.e 12345678901 would be a legitimate number to store in it. In reality it just means to justify the number on output with left padded spaces to 11 characters. Either way, it won't work in pgsql. Same goes for smallint(1) etc. ints don't have precision, and only MySQL supports a syntax that defines the OUTPUT of the type and not the actual precision, like it seems to. > 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', No such type as datetime. Perhaps you meant timestamp? Or maybe date (i.e. no time portion) > 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), The KEY keyword is not part of pgsql. You'll need to create separate indexes. > 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 I'd recommend just running it and fixing errors until it works. Here's the basic working version I came up with, might or might not do what you need: CREATE TABLE users ( user_id serial NOT NULL, 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 default NULL, date_created date NOT NULL, changed_by int default NULL, date_changed date default NULL, voided smallint NOT NULL default '0', voided_by int default NULL, date_voided timestamp default NULL, void_reason varchar(255) default NULL, 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);
Kusuma Pabba wrote:
> when creating tables,
>
> in my sql i have used create table
By "my SQL" I assume you mean MySQL? It took me a minute to figure out
that you didn't mean "in my sql code" but rather "in the product MySQL".
Having read your proposed table definition: I very strongly recommend
that you read the PostgreSQL manual, at least the parts on the query
language, data types, supported functions, and DML. It will save you a
lot of hassle and you will learn a lot.
> CREATE TABLE users (
> user_id int(11) NOT NULL serial,
serial is a pseudo-type. You'd use it instead of `int' here, eg:
user_id SERIAL
It's actually almost equivalent shorthand for:
CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
user_id integer default nextval('users_id_seq')
> date_created datetime NOT NULL default '0000-00-00 00:00:00',
PostgreSQL doesn't have a "datetime" type. You're probably looking for
"timestamp".
MySQL has a bizarre rule where the first datetime/timestamp column in a
table is automatically set to the time of record creation when a record
is inserted. PostgreSQL does not do this. You probably want something like:
date_created timestamp not null default current_timestamp,
A more robust approach that protects this field from being messed with
by the application would use a trigger (or, in Pg 8.4, column-level
permissions - HOORAY!).
> changed_by int(11) default NULL,
Pg doesn't have digit counts on integer types. Just use "integer", or if
you want a potentially really large value, use bigint.
> voided smallint(1) NOT NULL default '0',
You can use "boolean" here.
> KEY users_user_creator (creator),
Pg doesn't recognise this syntax. You're presumably trying to define an
index? If so, use CREATE INDEX after you define the table.
--
Craig Ringer
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Grzegorz Jaśkiewicz wrote: > just outside of your question, you should read about normalization. > it is in general very bad idea to have a table that holds all > information possible . > I wouldn't say this table is not normalized. The only fields you could move out of there would be the voided fields and I don't know if I would do that. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkmbsikACgkQjDX6szCBa+oorwCg76DkTMPmh+H6xkbgW+eO2I4m zR8AoNRVwbBNmS/vXyf5/bkC4TadUK07 =rgg8 -----END PGP SIGNATURE-----