Thread: Alter table column constraint

   I want to alter a term in a column's constraint to allow only specified
strings as attributes and have not found how to do this in the docs (using
version 10 docs now). There is an alter table command that allows renaming a
constraint but I've not seen how to modify the constraint itself.

   Pointer to a reference needed.

TIA,

Rich

On Mon, 17 Dec 2018, Rich Shepard wrote:

> I want to alter a term in a column's constraint to allow only specified
> strings as attributes and have not found how to do this in the docs (using
> version 10 docs now). There is an alter table command that allows renaming
> a constraint but I've not seen how to modify the constraint itself.

   Is the procedure to drop the current check constraint then add the revised
one?

Rich

On 12/17/18 12:01 PM, Rich Shepard wrote:
> On Mon, 17 Dec 2018, Rich Shepard wrote:
>
>> I want to alter a term in a column's constraint to allow only specified
>> strings as attributes and have not found how to do this in the docs 
>> (using
>> version 10 docs now). There is an alter table command that allows 
>> renaming
>> a constraint but I've not seen how to modify the constraint itself.
>
>   Is the procedure to drop the current check constraint then add the 
> revised
> one?

Or the other way around but yes.

JD


>
> Rich
>

-- 
Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc
***  A fault and talent of mine is to tell it exactly how it is.  ***
PostgreSQL centered full stack support, consulting and development.
Advocate: @amplifypostgres || Learn: https://postgresconf.org
*****     Unless otherwise stated, opinions are my own.   *****

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.

Regards,

Rich

On 12/17/2018 02: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.

Melvin is saying to:
1. create a table named valid_industry,
2. populate it with the valid industries,
3. create an FK constraint on your main table's industry column to 
valid_industry.industry, and then
4. drop the constraint invalid_industry.

-- 
Angular momentum makes the world go 'round.

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.

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

On Mon, 17 Dec 2018, Ron wrote:

> Melvin is saying to:
> 1. create a table named valid_industry,
> 2. populate it with the valid industries,
> 3. create an FK constraint on your main table's industry column to 
> valid_industry.industry, and then
> 4. drop the constraint invalid_industry.

   Got it. Hadn't before considered making column check constraints into
separate tables, but now I see the value of doing this.

Thanks, all,

Rich