How to make relationship between parent and child table in Postgresql

Question

I am learning postgresql, I am struggling while making relationship between parent table and child table. any help would be appreciated.

This is my parent table.

Table Name Country

c_id   state    No_of_cities    Total Population
1     state1    30                60
2     state2    40                70
3     state3    50                80

Here is my child's table details-

Table Name state1

s_id    cities  population
1       city1   234
2       city2   345

Table Name state2

s_id    cities  population
1      city3    544
2      city4    765

Table Name state3

s_id    cities  population
1      city1    543
2      city5    987

Please help me to figure out, how to make relationship between tables, So that I can get accurate data while query .

Answer 1

You need to have only one table for the cities of all states:

For example:

Table Name states

state    no_of_cities   population
state1   20             60
state2   30             70
state3   40             80

Table Name cities

state       cities   population
state1      city1    234
state1      city2    345
state2      city3    544
state2      city4    765
state3      city1    543
state3      city5    987

then you can add a foreign key

ALTER TABLE cities ADD FOREIGN KEY (state) REFERENCES states (state);

but first you must set the states primary key

ALTER TABLE states ADD PRIMARY KEY (state)

or (if you want a integer id as pk) add a unique key:

ALTER TABLE states ADD UNIQUE (state)