postgresql - SQL - Insert valid data based on predefined values -

i have 2 tables city , suburb. postgresql code:

create table city  (   id uuid primary key,   name character varying not null,   constraint city_id_key unique (id),   constraint city_name_key unique (name) )  create table suburb (   id uuid primary key,   city_id uuid not null,   name character varying not null,   constraint fk_suburb_city foreign key (city_id) references city (id),   constraint suburb_id_key unique (id),   constraint suburb_name_key unique (name) ) 

i want create table called address storing city + suburb pairs. here ddl address table:

create table address (   id uuid not null,   city_name character varying not null,   suburb_name character varying not null ) 

i want make sure redundant copies of information inserted address. here example:

i want allow inserting address city_name suburb_name pairs:

select c.name city_name, s.name suburb_name   city c, suburb s  c.id = s.city_id 

result:

a - b - c x - y 

for data above want allow pairs:

a - b - c x - y 

but if wants insert - y pair address, want dbms raise error/exception.

questions:

1. does make sense check constraint this?

2. if valid idea, best solution this? trigger, stored procedure, kind of constraint?

i prefer dbsm independent solutions. i'm more interested in basic idea of suggested solution not in postgresql specific solution.

reflecting @yuri g's answer: don't want join when read form address. want store real values in not ids. slow insert address not problem. fast read important me. change in city or suburb table not problem after insertion in address. no need update in address table. want make sure data insert address valid city - suburb pair (according city , suburb tables).

my plan upload city , suburb tables lots of data , use them validating insertions in address table. don't want allow users insert address example: "new york - fatima bint mubarak st" because fatima bint mubarak st. in abu dhabi.

thank answers.

let software on client side operate record identifiers city & suburb, not values.

and on server side too:

create table address (   id uuid not null,   city_id character varying not null,   suburb_id character varying not null,   constraint fk_city foreign key (city_id) references city (id),   constraint fk_suburb foreign key (suburb_id) references suburb (id), ) 

of course, you'll need 2 lookup operations prior insert, 2 selects name of city/suburb, retrieve these ids (or deny operation).

though way you'd keeping data integrity simple & efficient way, believe.