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.
