Re: Alter table column constraint

On 12/17/18 12:20 PM, Rich Shepard 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.

What Melvin suggested was to:

1) Move this ('Agriculture', 'Business, other', 'Chemicals', ..)
into its own table say something like:

CREATE TABLE industry(industry_code varchar PRIMARY KEY, industry_desc
varchar)

2) Change the industry field in your existing table to:

industry varchar(24) NOT NULL REFERENCES industry(industry_code) ON
UPDATE CASCADE.

Where this helps is that in the situation you describe in your original
post you just change 'Municipalities' to 'Government' in the industry
table and the referring table automatically gets the change via the ON
UPDATE CASCADE.

>
> Regards,
>
> Rich
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com