Alter table add foreign key postgres10/11/2023 The main reason for providing the option to specify multiple changes in a single ALTER TABLE is that multiple table scans or rewrites can thereby be combined into a single pass over the table. My reason for thinking this is from the Postgres documentation for the ALTER TABLE statement where in the Notes section it reads This takes 520 seconds whereas I expected it to at least take less than 450 seconds. create table identitynumber( id int not null, PRIMARY KEY (id) ) persondetails. ![]() alter table only public.eventsĪDD CONSTRAINT events_pkey PRIMARY KEY (id),ĪDD CONSTRAINT events_user_id1_fkey FOREIGN KEY (user_id1) REFERENCES ers(id),ĪDD CONSTRAINT events_user_id2_fkey FOREIGN KEY (user_id2) REFERENCES ers(id),ĪDD CONSTRAINT events_unique_int1_me_key UNIQUE (unique_int1),ĪDD CONSTRAINT events_unique_int2_me_key UNIQUE (unique_int2), I expected that when I combined the 5 above statements into a single ALTER TABLE statement that the time would be reduced but in fact it has increased. But PostgreSQL has a non-standard extension that lets you use multiple constraint clauses in a single SQL statement. Repeat for each foreign key you want to change. I then wish to apply the following constraints via 5 individual alter table statements: ALTER TABLE ONLY public.eventsĪDD CONSTRAINT events_pkey PRIMARY KEY (id) ĪDD CONSTRAINT events_user_id1_fkey FOREIGN KEY (user_id1) REFERENCES ers(id) ĪDD CONSTRAINT events_user_id2_fkey FOREIGN KEY (user_id2) REFERENCES ers(id) ĪDD CONSTRAINT events_unique_int1_me_key UNIQUE (unique_int1) ĪDD CONSTRAINT events_unique_int2_me_key UNIQUE (unique_int2) ĪDD CONSTRAINT events_unique_int3_me_key UNIQUE (unique_int3) Įach of the above statements takes approximately 90 seconds to run for a total of 450 seconds. start a transaction, drop the foreign key, add a foreign key with on delete cascade, and finally. ALTER TABLE inventory ADD CONSTRAINT fkinvproductid FOREIGN KEY (productid) REFERENCES products (productid) ON DELETE CASCADE. As a simple test I have the following setup: CREATE TABLE usersĮvents (user_id1,user_id2,unique_int1,unique_int2,unique_int3) After data finishes loading we go and apply the necessary constraints back to the database. ![]() The bulk load is performed into a table with all primary keys, indexes, and foreign keys dropped.
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |