I want to maintain two tables primary and secondary. The API services serves the clients from the primary table. When i have to repopulate the tables i write to a new table and change the secondary and primaries like below.
BEGIN;
DROP TABLE "public.table_secondary"
ALTER TABLE "public.table_primary" RENAME TO "public.table_secondary";
ALTER TABLE "public.table_new" RENAME TO "public.table_primary";
COMMIT;
This way the API service wont go down when the tables are repopulated. The question that i have is what happens to constraints, indexes and sequence used for the table_primary and table_secondary. For the table_primary, the additional DDL statements used during creation are:
CREATE SEQUENCE public.seq1
ALTER TABLE public.table_primary ADD COLUMN id bigint NOT NULL DEFAULT nextval('seq1');
ALTER TABLE ONLY public.table_primary ADD CONSTRAINT pkeyconstraint PRIMARY KEY (id);
ALTER TABLE public.table_primary ADD CONSTRAINT uniqueidfk FOREIGN KEY (uniqueid) REFERENCES externaltable2 (id);
CREATE INDEX IF NOT EXISTS index ON table_primary (uniqueid);
How should the additional constraints and indexes handled when we do a swap of the tables?
Thanks in advance for reply