As foreign key reference alternating tables

Question

I have a City, State and Country Database. But I want to add Cities to my table and it doesn’t always contain a state to be referenced.

However, every city is located in a country.

My question is: can I create a relationship in which the city that has not been, but which may be related to some country?

CREATE TABLE cidade
(
  id_cidade integer NOT NULL,
  id_estado integer NOT NULL,
  nome character varying(120) NOT NULL,
  CONSTRAINT cidade_pkey PRIMARY KEY (id_cidade),
  CONSTRAINT fk_cidade_estado FOREIGN KEY (id_estado)
      REFERENCES estado (id_estado) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Answer 1

By the model presented, you will always have to provide a status, since ID_ESTADO is NOT NULL.

I, in your case, would create a state called SEM_ESTADO or NA (NOT APPLICABLE) for each country, so it would be possible to treat everything the same way and it would be easy to know, for example, all cities that do not have a state.

In other words, don’t change your model because it makes sense. Just create a fictional state to suit cities that have no state.

Answer 2

If it is impossible to determine the country of all cities by their state, you should forget the state/country relationship when seeking information from a city, instead you should create a city/country relationship...

id_pais integer NOT NULL