Received: from megazone.bigpanda.com (megazone.bigpanda.com [63.150.15.178]) by postgresql.org (Postfix) with ESMTP id 53AE64759CA for ; Thu, 21 Feb 2002 14:52:05 -0500 (EST) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id C30FBD5F9; Thu, 21 Feb 2002 11:52:05 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id B89425BF9; Thu, 21 Feb 2002 11:52:05 -0800 (PST) Date: Thu, 21 Feb 2002 11:52:05 -0800 (PST) From: Stephan Szabo To: Josh Berkus Cc: , pgsql sql-list Subject: Re: Changing column constraints? In-Reply-To: Message-ID: <20020221114936.W21441-100000@megazone23.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200202/276 X-Sequence-Number: 6699 On Thu, 21 Feb 2002, Josh Berkus wrote: > Henry, > > > If I have a column constraint: > > addr_type char( 10 ) default 'street' > > CHECK( addr_type = 'street' || addr_type = 'pobox' ), > > in a table, how can I add another choice to the list after I've > > already > > entered data into the table? > > Three options: > > One, drop and rebuild the table with the constraint modified. Sorry, > but until we have ALTER CONSTRAINT implemented, this is the only > simple way to modify and exisiting constraint. Umm... on 7.2 you should be able to drop the existing constraint and add a new one. If you don't name the constraint you have to find the internally generated name for the constraint from pg_relcheck however. > Three, drop and re-build the table, and this time do it right: have > column addr_type reference another table called "addr_types" that list > the acceptable type values. Then use a foriegn key to enforce this. This is probably happier in any case...