How To Reference Foreign Key Alternating Tables

Question

How To Reference Foreign Key Alternating Tables

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I have a Database of City, State and Country. But I want to add Cities to my table and it does not always contain a State to be referenced.

However, every city is located in one country.

My question is: can I create a relationship in which a city that has not been, but which may be related to a 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
)

modelo-relacional postgresql chave-estrangeira

asked by anonymous 05.08.2015 / 21:07

2 answers

Intel XDK doubts Recover Date of type Date - php + mysql

score 1 · Answer 1

For the model shown, you will always have to supply a state, 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 in the same way and it would be easy to know, for example, all cities that do not have a state.

In other words, do not change your model because it makes sense. Just create a fictitious state to serve cities that have no state.

score 0 · Answer 2

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

id_pais integer NOT NULL