Re: Alter table column constraint

On Mon, Dec 17, 2018 at 1:20 PM Rich Shepard <rshepard@appl-ecosys.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.