Обсуждение: Renaming tables
Hi,
I'm running postgres 7.0.3 on Sun 220R (1Gig Ram) with Solaris2.8 (upgrading to 7.1.3 or 7.2 very soon)
I have several tables I am planning on renaming and ran into a little trouble. I know I can fix the problem, but I'm hoping there is an easier solution. Here is an example: I rename usergroupstatus to usergroupstatusvalues. I did a dump of the this table as well as a table that has a Fkey to this table:
CREATE TABLE "usergroupstatusvalues" (
"status" character varying(25) NOT NULL,
PRIMARY KEY ("status")
);
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroupstatusvalues" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'usergroup', 'usergroupstatus', 'UNSPECIFIED', 'status', 'status');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroupstatusvalues" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'usergroup', 'usergroupstatus', 'UNSPECIFIED', 'status', 'status');
"status" character varying(25) NOT NULL,
PRIMARY KEY ("status")
);
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroupstatusvalues" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'usergroup', 'usergroupstatus', 'UNSPECIFIED', 'status', 'status');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroupstatusvalues" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'usergroup', 'usergroupstatus', 'UNSPECIFIED', 'status', 'status');
CREATE TABLE "usergroup" (
"id" numeric(11,0),
"name" character varying(50) NOT NULL,
"groupid" character varying(2) NOT NULL,
"issystemadmin" numeric(1,0),
"lastupdatedatetime" character varying(50),
"lastupdateuserid" character varying(30),
"recordstatuscode" character varying(2) NOT NULL,
"location" character varying(5) NOT NULL,
"status" character varying(25) NOT NULL,
"timestamp" character varying(30),
PRIMARY KEY ("groupid")
);
"id" numeric(11,0),
"name" character varying(50) NOT NULL,
"groupid" character varying(2) NOT NULL,
"issystemadmin" numeric(1,0),
"lastupdatedatetime" character varying(50),
"lastupdateuserid" character varying(30),
"recordstatuscode" character varying(2) NOT NULL,
"location" character varying(5) NOT NULL,
"status" character varying(25) NOT NULL,
"timestamp" character varying(30),
PRIMARY KEY ("groupid")
);
CREATE UNIQUE INDEX "usergroup_id_unique" on "usergroup" using btree ( "id" "numeric_ops" );
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'usergroup', 'recordstatuscodes', 'UNSPECIFIED', 'recordstatuscode', 'code');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'usergroup', 'usergroupstatus', 'UNSPECIFIED', 'status', 'status');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'usergroupsignals', 'usergroup', 'UNSPECIFIED', 'id', 'id');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'usergroupsignals', 'usergroup', 'UNSPECIFIED', 'id', 'id');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'usertable', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'usertable', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'securityaccessprofile', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'securityaccessprofile', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'usergroup', 'recordstatuscodes', 'UNSPECIFIED', 'recordstatuscode', 'code');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('<unnamed>', 'usergroup', 'usergroupstatus', 'UNSPECIFIED', 'status', 'status');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'usergroupsignals', 'usergroup', 'UNSPECIFIED', 'id', 'id');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'usergroupsignals', 'usergroup', 'UNSPECIFIED', 'id', 'id');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'usertable', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'usertable', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER DELETE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_del" ('<unnamed>', 'securityaccessprofile', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER UPDATE ON "usergroup" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_noaction_upd" ('<unnamed>', 'securityaccessprofile', 'usergroup', 'UNSPECIFIED', 'groupid', 'groupid');
The constraints still refer to the original table name (usergroupstatus). I know that I can drop and re-create the tables and have them refer to the new table, but I'm hoping there is a better way. Also, if I need to recreate the tables should I use the statements above (generated by pg_dump) and manually modify the constraints?
Thanks in advance!
Tim DeMarco
On Mon, 11 Mar 2002, Tim DeMarco wrote: > I have several tables I am planning on renaming and ran into a little > trouble. I know I can fix the problem, but I'm hoping there is an > easier solution. Here is an example: I rename usergroupstatus to > usergroupstatusvalues. I did a dump of the this table as well as a > table that has a Fkey to this table: > > The constraints still refer to the original table name > (usergroupstatus). I know that I can drop and re-create the tables > and have them refer to the new table, but I'm hoping there is a better > way. Also, if I need to recreate the tables should I use the > statements above (generated by pg_dump) and manually modify the > constraints? It might be easier to drop the triggers and use alter table to add the foreign key constraints again.