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.klaver@aklaver.com