Thread: Alter table column constraint

Alter table column constraint

From
Rich Shepard
Date:
   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



Re: Alter table column constraint

From
Rich Shepard
Date:
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


Re: Alter table column constraint

From
"Joshua D. Drake"
Date:
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.   *****



Re: Alter table column constraint

From
Melvin Davidson
Date:
>I want to alter a term in a column's constraint to allow only specified
> strings as attributes
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.

On Mon, Dec 17, 2018 at 3:07 PM Joshua D. Drake <jd@commandprompt.com> wrote:
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.   *****




--
Melvin Davidson
Maj. Database & Exploration Specialist

Universe Exploration Command – UXC

Employment by invitation only!

Re: Alter table column constraint

From
Rich Shepard
Date:
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


Re: Alter table column constraint

From
Ron
Date:
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.


Re: Alter table column constraint

From
"David G. Johnston"
Date:
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.


Re: Alter table column constraint

From
Melvin Davidson
Date:
So CREATE a table eg:
CREATE TABLE fks_for_tables
( fks_id serial
  fks_values varchar(20),
 CONSTRAINT fks_pkey PRIMARY KEY (fks_id),
 CONSTRAINT fks-unique UNIQUE fks_values
)

Then
INSERT INTO fks_for_tables
(fks_values)
VALUES
( 'Agriculture'),
('Business'),
('other))',
'Chemicals')
...
...
('Transportation');

Then you can
ALTER TABLE your_table
ADD CONSTRAINT FOREIGN KEY (industry)
REFERENCES fks_for_tables(fks_valies);




On Mon, Dec 17, 2018 at 3: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.

Regards,

Rich



--
Melvin Davidson
Maj. Database & Exploration Specialist

Universe Exploration Command – UXC

Employment by invitation only!

Re: Alter table column constraint

From
Melvin Davidson
Date:

On Mon, Dec 17, 2018 at 3:32 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
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.



--
Melvin Davidson
Maj. Database & Exploration Specialist

Universe Exploration Command – UXC

Employment by invitation only!

Re: Alter table column constraint

From
Adrian Klaver
Date:
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


Re: Alter table column constraint [RESOLVED]

From
Rich Shepard
Date:
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