Alter table column constraint

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Alter table column constraint

Rich Shepard
   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


Reply | Threaded
Open this post in threaded view
|

Re: Alter table column constraint

Rich Shepard
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

Reply | Threaded
Open this post in threaded view
|

Re: Alter table column constraint

Joshua D. Drake
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.   *****


Reply | Threaded
Open this post in threaded view
|

Re: Alter table column constraint

Melvin Davidson-5
>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 <[hidden email]> 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!
Reply | Threaded
Open this post in threaded view
|

Re: Alter table column constraint

Rich Shepard
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

Reply | Threaded
Open this post in threaded view
|

Re: Alter table column constraint

Ron-2
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.

Reply | Threaded
Open this post in threaded view
|

Re: Alter table column constraint

David G Johnston
In reply to this post by Rich Shepard
On Mon, Dec 17, 2018 at 1:20 PM Rich Shepard <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Alter table column constraint

Melvin Davidson-5
In reply to this post by Rich Shepard
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 <[hidden email]> 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!
Reply | Threaded
Open this post in threaded view
|

Re: Alter table column constraint

Melvin Davidson-5
In reply to this post by David G Johnston

On Mon, Dec 17, 2018 at 3:32 PM David G. Johnston <[hidden email]> wrote:
On Mon, Dec 17, 2018 at 1:20 PM Rich Shepard <[hidden email]> 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!
Reply | Threaded
Open this post in threaded view
|

Re: Alter table column constraint

Adrian Klaver-4
In reply to this post by Rich Shepard
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Alter table column constraint [RESOLVED]

Rich Shepard
In reply to this post by Ron-2
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