CODEWITHSUNDEEP
sqlpostgresql
thegreat_one
thegreat_one

Reputation: 11

How do I make a foreign key to an already created PostgreSQL table?

I have two tables table 1:

cs111=# \d structure;
              Table "public.structure"
  Column  |  Type   | Collation | Nullable | Default 
----------+---------+-----------+----------+---------
 page_url | text    |           |          | 
 arm_id   | integer |           |          | 
Indexes:
    "structure_arm_id_key" UNIQUE CONSTRAINT, btree (arm_id)

and table 2:

cs111=# \d bandit_pages;
              Table "public.bandit_pages"
   Column    |  Type   | Collation | Nullable | Default 
-------------+---------+-----------+----------+---------
 campaign_id | text    |           |          | 
 arm_id      | integer |           |          | 
 status      | boolean |           |          |

I want to make arm_id from structure table to be a foreign key for bandit_pages table but on the column of campaign_id. These tables are already made so I am unsure how to alter these tables. Please help.

Upvotes: 0

Views: 112

Answers (1)

user330315
user330315

Reputation:

The whole questions seems to be going in the wrong direction, but anyway.

Under the following assumptions:

  1. campaign_id is actually a number incorrectly stored as a string
  2. campaign_id does indeed store the value of structure.arm_id

then, you can create the foreign key using the following steps:

-- convert campaign_id to a proper integer
alter table bandit_pages
  alter campaign_id type integer using campaign_id::int;

-- make campaign_id unique 
alter table bandit_pages
  add constraint unique_campaign_id unique (campaign_id);
  
--- create the foreign key
alter table structure
   add constraint fk_structure_bandit_pages 
   foreign key (arm_id) references bandit_pages (campaign_id);

Upvotes: 1

Related Questions