How to instantiate a constructor variable (to be Primary key) within the table?

Question

The question is misspelled, but the problem is this. I’m working with BDOR in the Postgresql. I created two Types and I’m adding them to Type animal. Soon after, I used Inheritance and created the table nipples animal-type. Only I want to use the attribute name inside the type dono_ty as Primary key. I want to use Primary key, because the same person (even Cpf) can register in the tables mamiferos, birds and reptiles (I will create two more), then I would like to make a select of that information. How could I resolve?

I’ve tried the following ways too:

CREATE TABLE mamiferos OF animal(Cpf Primary key);
CREATE TABLE mamiferos OF animal(dono.Cpf Primary key);

Answer 1

Inheritances are beautiful in theory, but are a hell in practice. An excellent alternative is the use of a coluna discriminadora, which is actually a foreign key that points to the type of that animal.

See how your idea can be structured differently:

CREATE TABLE tb_porte (
  id INTEGER PRIMARY KEY,
  descricao TEXT
);

CREATE TABLE tb_tipo_animal (
  id INTEGER PRIMARY KEY,
  descricao TEXT
);

CREATE TABLE tb_dono (
  id INTEGER PRIMARY KEY,
  nome TEXT
);

CREATE TABLE tb_animal (
  id SERIAL PRIMARY KEY,
  nome TEXT,
  especie TEXT,
  id_porte INTEGER REFERENCES tb_porte (id),
  id_dono INTEGER REFERENCES tb_dono (id),
  id_tipo INTEGER REFERENCES tb_tipo_animal (id), -- Coluna Discriminadora
  dt_consulta DATE,
  diagnostico TEXT
);

Populating tables:

-- PORTE DO ANIMAL
INSERT INTO tb_porte (id, descricao) VALUES ( 1, 'PEQUENO');
INSERT INTO tb_porte (id, descricao) VALUES ( 2, 'MEDIO');
INSERT INTO tb_porte (id, descricao) VALUES ( 3, 'GRANDE');

-- TIPO DO ANIMAL
INSERT INTO tb_tipo_animal (id, descricao) VALUES ( 1, 'MAMIFERO');
INSERT INTO tb_tipo_animal (id, descricao) VALUES ( 2, 'AVE');
INSERT INTO tb_tipo_animal (id, descricao) VALUES ( 3, 'REPTIL');

-- DONOS
INSERT INTO tb_dono (id, nome) VALUES ( 1, 'WALT DISNEY');
INSERT INTO tb_dono (id, nome) VALUES ( 2, 'SUPER MARIO');
INSERT INTO tb_dono (id, nome) VALUES ( 3, 'WALTER LANTZ');
INSERT INTO tb_dono (id, nome) VALUES ( 4, 'CEBOLINHA');

-- ANIMAIS
INSERT INTO tb_animal ( nome, especie, id_dono, id_tipo, id_porte ) VALUES ('MICKEY',      'RATO',        1, 1, 2 );
INSERT INTO tb_animal ( nome, especie, id_dono, id_tipo, id_porte ) VALUES ('PATETA',      'CACHORRO',    1, 1, 2 );
INSERT INTO tb_animal ( nome, especie, id_dono, id_tipo, id_porte ) VALUES ('DONALD',      'PATO',        1, 2, 2 );
INSERT INTO tb_animal ( nome, especie, id_dono, id_tipo, id_porte ) VALUES ('PICA-PAU',    'PASSARO',     3, 2, 1 );
INSERT INTO tb_animal ( nome, especie, id_dono, id_tipo, id_porte ) VALUES ('WALLY GATOR', 'JACARE',      3, 3, 3 );
INSERT INTO tb_animal ( nome, especie, id_dono, id_tipo, id_porte ) VALUES ('YOSHI',       'DINOSSAURO',  2, 3, 3 );
INSERT INTO tb_animal ( nome, especie, id_dono, id_tipo, id_porte ) VALUES ('BIDU',        'CACHORRO',    4, 1, 1 );

Example query:

SELECT
  a.nome AS no_animal,
  d.nome AS no_dono,
  a.especie AS ds_especie,
  t.descricao AS no_tipo,
  p.descricao AS ds_porte
FROM
  tb_animal AS a
LEFT JOIN
  tb_dono AS d ON (d.id = a.id_dono)
LEFT JOIN
  tb_tipo_animal AS t ON (t.id = a.id_tipo)
LEFT JOIN
  tb_porte AS p ON (p.id = a.id_porte)

Exit:

|   no_animal |      no_dono | ds_especie |  no_tipo | ds_porte |
|-------------|--------------|------------|----------|----------|
|      MICKEY |  WALT DISNEY |       RATO | MAMIFERO |    MEDIO |
|      PATETA |  WALT DISNEY |   CACHORRO | MAMIFERO |    MEDIO |
|      DONALD |  WALT DISNEY |       PATO |      AVE |    MEDIO |
|    PICA-PAU | WALTER LANTZ |    PASSARO |      AVE |  PEQUENO |
| WALLY GATOR | WALTER LANTZ |     JACARE |   REPTIL |   GRANDE |
|       YOSHI |  SUPER MARIO | DINOSSAURO |   REPTIL |   GRANDE |
|        BIDU |    CEBOLINHA |   CACHORRO | MAMIFERO |  PEQUENO |

See working on Sqlfiddle

Reference: Inheritance in relational database