![]() ![]() Solution for changing options on many tables. It only takes foreign table name / column name, drops current key and replaces with new one. ' -> ' || reftable || '.' || refcolumn || ') New options: ' || new_options īe aware: this function won't copy attributes of initial foreign key. RETURN 'Constraint replaced: ' || constraint_name || ' (' || f_table || '.' || f_column || ' REFERENCES ' || reftable || '(' || refcolumn || ') ' || new_options || ' ' ', ADD CONSTRAINT ' || constraint_name || ' FOREIGN KEY (' || f_column || ') ' || INTO constraint_name, reftable, refcolumn ĮXECUTE 'alter table ' || f_table || ' drop constraint ' || constraint_name || ON ccu.constraint_name = tc.constraint_nameĪND tc.table_name= f_table AND kcu.column_name= f_column JOIN information_nstraint_column_usage AS ccu ON tc.constraint_name = kcu.constraint_name JOIN information_schema.key_column_usage AS kcu Information_schema.table_constraints AS tc SELECT tc.constraint_name, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name Replace_foreign_key(f_table VARCHAR, f_column VARCHAR, new_options VARCHAR) Usage: select replace_foreign_key('user_rates_posts', 'post_id', 'ON DELETE CASCADE') Where position_in_unique_constraint is not null If you don't know the name of the foreign key constraint you want to drop, you can either look it up in pgAdminIII (just click the table name and look at the DDL, or expand the hierarchy until you see "Constraints"), or you can query the information schema. Repeat for each foreign key you want to change.īut PostgreSQL has a non-standard extension that lets you use multiple constraint clauses in a single SQL statement. add a foreign key with on delete cascade, and finally.In standard SQL, I believe the easiest way to do this is to You have to drop the constraint first, then add the correct version. I'm pretty sure you can't simply add on delete cascade to an existing foreign key constraint. UPDATE: Thank you, and also I've got the advice at the mailing list, that I could manage it in 1 statement and thus without explicitly starting a transaction: ALTER TABLE public.scores TABLE "status" CONSTRAINT "status_id_fkey" FOREIGN KEY (id) REFERENCES users(id)Īnd also I wonder if it makes sense to add 2 index'es to the former table? TABLE "scores" CONSTRAINT "scores_id_fkey" FOREIGN KEY (id) REFERENCES users(id) ![]() TABLE "rep" CONSTRAINT "rep_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "rep" CONSTRAINT "rep_author_fkey" FOREIGN KEY (author) REFERENCES users(id) TABLE "payment" CONSTRAINT "payment_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "pass" CONSTRAINT "pass_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "money" CONSTRAINT "money_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "misere" CONSTRAINT "misere_id_fkey" FOREIGN KEY (id) REFERENCES users(id) ![]() TABLE "match" CONSTRAINT "match_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "luck" CONSTRAINT "luck_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "hand" CONSTRAINT "hand_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "game" CONSTRAINT "game_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "chat" CONSTRAINT "chat_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "catch" CONSTRAINT "catch_id_fkey" FOREIGN KEY (id) REFERENCES users(id) TABLE "cards" CONSTRAINT "cards_id_fkey" FOREIGN KEY (id) REFERENCES users(id) Login | timestamp without time zone | default now() TABLE "scores" CONSTRAINT "scores_gid_fkey" FOREIGN KEY (gid) REFERENCES games(gid) Gid | integer | not null default nextval('games_gid_seq'::regclass)įinished | timestamp without time zone | default now() "scores_id_fkey" FOREIGN KEY (id) REFERENCES users(id)īoth referenced tables are below - here: # \d games "scores_gid_fkey" FOREIGN KEY (gid) REFERENCES games(gid) In PostgreSQL 8 is it possible to add ON DELETE CASCADES to the both foreign keys in the following table without dropping the latter? # \d scores ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |