How to insert into foreign key columns in postgresql?

Question

Maybe this is a stupid question, but I'm kind of stuck here... So I have a table A

state | city
MI     Detroit
...

And I have a state table B

state_id | state
1           MI
...

And a city table C

city_id | city | state_id
1        Detroit  NULL

I want to add a foreign key column in city table, I've already created a foreign key column in it with the syntax ALTER TABLE city ADD CONSTRAINT city_state FOREIGN KEY (state_id) REFERENCES state(state_id); But the foreign key column was filled with null since it doesn't know the relation between city table and the state table.

How can I fill in the foreign key column with right state id? Can someone help?

Please clarify your question. What is the newly created foreign key column? A foreign key is a constraint on what data you can store, it doesn't create any new columns or data. Also, your tags are conflicting (mysql & postgresql). — Henning Koehler
– Henning Koehler, Commented Dec 1, 2018 at 1:34
I've updated the question. The city table should be city_id, city, state_id. But with the above ALTER TABLE syntax the state_id column is only filled with NULL — Chuyao Wang
– Chuyao Wang, Commented Dec 1, 2018 at 1:37
Now your design doesn't make much sense. I'd think you'd want one table with (state_id, state) and one table (city, state_id). — Henning Koehler
– Henning Koehler, Commented Dec 1, 2018 at 1:51

Gordon Linoff · Accepted Answer · 2018-12-01 01:39:16Z

3

You can use join in an update. In Postgres this looks like:

update city
   set state_id = b.state_id
   from a join
        b
        on a.state = b.state
   where city.city = a.city;

In MySQL:

update city join
       a
       on city.city = a.city join
       b
       on a.state = b.state
   set city.state_id = b.state_id;

answered Dec 1, 2018 at 1:39

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Sebastián Sethson · Accepted Answer · 2018-12-01 01:49:07Z

-1

You SQL code for Mysql is:

CREATE TABLE city (
    city_id int NOT NULL,
    name varchar(255) NOT NULL,
    state_id int,
    PRIMARY KEY (city_id),
    FOREIGN KEY (state_id) REFERENCES state(state_id)
);

And if you want add a new row with a reference to another row in the state`s table, you need add the id of this row, example:

INSERT INTO city VALUES  (1, "Los Angeles", 1);

1 will be a id from state table!

answered Dec 1, 2018 at 1:49

Sebastián Sethson

112 bronze badges

Collectives™ on Stack Overflow

How to insert into foreign key columns in postgresql?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related