pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Changing column constraints?
3+ messages / 3 participants
[nested] [flat]

* Changing column constraints?
@ 2002-02-21 09:25  Henry Holland <spoedniek@gmx.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Henry Holland @ 2002-02-21 09:25 UTC (permalink / raw)
  To: pgsql-sql

Hi,

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?

Henry







^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Changing column constraints?
@ 2002-02-21 19:07  Josh Berkus <josh@agliodbs.com>
  parent: Henry Holland <spoedniek@gmx.net>
  0 siblings, 1 reply; 3+ messages in thread

From: Josh Berkus @ 2002-02-21 19:07 UTC (permalink / raw)
  To: pgsql-sql

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.

Two, mess with system tables to alter the constraint.  I do not
 reccomend this.

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.

-Josh Berkus
 



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: Changing column constraints?
@ 2002-02-21 19:52  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Stephan Szabo @ 2002-02-21 19:52 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: hjholland@gmx.net; pgsql-sql


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...




^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2002-02-21 19:52 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-02-21 09:25 Changing column constraints? Henry Holland <spoedniek@gmx.net>
2002-02-21 19:07 ` Josh Berkus <josh@agliodbs.com>
2002-02-21 19:52   ` Stephan Szabo <sszabo@megazone23.bigpanda.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox