Re: Alter table column constraint [RESOLVED]
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
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.kla...@aklaver.com
Re: Alter table column constraint
See https://www.postgresql.org/docs/current/tutorial-fk.html
On Mon, Dec 17, 2018 at 3:32 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Mon, Dec 17, 2018 at 1: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.
>
> --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
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
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
On Mon, Dec 17, 2018 at 1: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.
--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
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
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
>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 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
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
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
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
