On Thu, Feb 19, 2009 at 11:43:19PM +0000, Sam Mason wrote: > I was just reading over a reply from David Fetter from a couple of > days ago; the thread is archived[1] but this question doesn't really > relate to it much. The a question about how to arrange tables and > David make the following comments: > > On Tue, Feb 17, 2009 at 09:53:00AM -0800, David Fetter wrote: > > 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. > > The reason behind this appears to be moving some of the checks into > the database and away from the application. Since a useful database has *many* applications instead of "the" application, I think this is an excellent move. Single Point of Truth and all that. > When I've solved similar problems before, I've tended to make the > application more aware of what's going on by having something like: > > user_name VARCHAR(50) NOT NULL UNIQUE > CHECK (user_name ~ '^[a-z][a-z0-9_]*$') My point there was that simply limiting the length isn't enough for many purposes, and when you're adding DOMAIN or other constraints on the value, that's a place to put the length checks in, too. For example, you might well want to set a lower bound on the size of a user_name, not just an upper bound. > I don't think that either my nor David's is better in general, they > apply to different situations. I don't even think they're *different* in general ;) Cheers, David. -- David Fetter <david@xxxxxxxxxx> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@xxxxxxxxx Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
