Re: Alter table column constraint

See https://www.postgresql.org/docs/current/tutorial-fk.html

On Mon, Dec 17, 2018 at 3:32 PM David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Mon, Dec 17, 2018 at 1:20 PM Rich Shepard <rshepard(at)appl-ecosys(dot)com>
> wrote:
> >
> > On Mon, 17 Dec 2018, Melvin Davidson wrote:
> >
> > > Yes, you must drop then add the revised constraint. However, from your
> > > statement above, it sounds to me as if you would be better off using A
> > > FOREIGN kEY CONSTRAINT. It makes things a lot simpler.
> >
> > Melvin,
> >
> > I don't follow. Here's the DDL for that column:
> >
> > industry varchar(24) NOT NULL
> > CONSTRAINT invalid_industry
> > CHECK (industry in ('Agriculture', 'Business, other', 'Chemicals',
> > 'Energy', 'Law', 'Manufacturing', 'Mining', 'Municipalities',
> > 'Ports/Marine Services', 'Transportation')),
> >
> > and I want to remove Municipalities for the more general Government.
>
> --not tested
>
> CREATE TABLE industry (
> industry_name text PRIMARY KEY
> );
>
> CREATE TABLE company (
> company_id serial PRIMARY KEY,
> industry_name text REFERENCES industry (industry_name)
> ON UPDATE CASCADE
> ON DELETE RESTRICT
> );
>
> UPDATE industries SET industry_name = 'Government' WHERE industry_name
> = 'Municipalities';
> -- All records in company have changed now too thanks to the ON UPDATE
> CASCADE
>
> To avoid the effective table rewrite use surrogate keys and turn the
> text into a simple label. It should still have a UNIQUE index on it
> though as it is your real key.
>
> David J.
>
>

--
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!