CODEWITHSUNDEEP
sqlpostgresql
Chuyao Wang
Chuyao Wang

Reputation: 205

How to insert into foreign key columns in postgresql?

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?

Upvotes: 2

Views: 3515

Answers (2)

Sebastián Sethson
Sebastián Sethson

Reputation: 11

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!

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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;

Upvotes: 3

Related Questions