Validate only 1 and 2 in an sql table in the relational and logical model?

Asked

Viewed 73 times

0

Good morning, as I do to validate values using relational and logical relationships , where the employee can only enter values below?

1: Disponível 
2: Emprestado
postgresql

  • There’s just no way to understand what you need. The only thing understandable is that you need to validate something. Can you describe the problem better? Maybe the [Ask] guide can be useful to you.

  • Friend, here’s the thing, I have a physical model with several tables, only when I have to put 1 to available value and 2 to borrowed within the table create , in the table I have can only create these values once, that it comes to author’s work rentals , and only has a situation in a copy , or he is borrowed or available , understood ?

  • Still confused, it would be easier if you put the table model in the question to exemplify

  • From what I understand you want to validate the domain of a field, which only has values 1 and 2, is this?

  • That’s right, buddy, like I do that ?

2 answers

1

You can use a foreign key for a domain table containing all valid and/or acceptable values.

Imagine a hypothetical scenario where you want to control book lending. In this scenario, a Livro can only be Disponível or Emprestado.

The model would be something like:

inserir a descrição da imagem aqui

Translating to Pg/SQL:

-- POSSIVEIS STATUS DE UM LIVRO
CREATE TABLE tb_status
(
    id BIGINT PRIMARY KEY,
    descricao TEXT
);

-- LIVROS
CREATE TABLE tb_livro
(
    id BIGINT PRIMARY KEY,
    id_status BIGINT,
    titulo TEXT,
    autor TEXT,
    FOREIGN KEY ( id_status ) REFERENCES tb_status( id )
);

Registering as many as possible Status of a Livro:

INSERT INTO tb_status ( id, descricao ) VALUES ( 1, 'Disponível' );
INSERT INTO tb_status ( id, descricao ) VALUES ( 2, 'Emprestado' );

Registering Livros with Status of Disponível:

INSERT INTO tb_livro ( id, id_status, titulo, autor ) VALUES ( 100, 1, 'Memórias Póstumas de Brás Cubas', 'Machado de Assis' );
INSERT INTO tb_livro ( id, id_status, titulo, autor ) VALUES ( 200, 1, 'Macunaíma', 'Mário de Andrade' );

Registering Livros with Status of Emprestado:

INSERT INTO tb_livro ( id, id_status, titulo, autor ) VALUES ( 300, 2, 'Vidas Secas', 'Graciliano Ramos' );
INSERT INTO tb_livro ( id, id_status, titulo, autor ) VALUES ( 400, 2, 'Fogo Morto', 'José Lins do Rego' );
INSERT INTO tb_livro ( id, id_status, titulo, autor ) VALUES ( 500, 2, 'Grande Sertão: Veredas', 'Guimarães Rosa' );

Return of books:

-- DEVOLUCAO DO LIVRO: "GRANDE SERTAO VEREDAS"
UPDATE tb_livro SET id_status = 1 WHERE id = 500;

-- DEVOLUCAO DO LIVRO: "VIDAS SECAS"
UPDATE tb_livro SET id_status = 1 WHERE id = 300;

Loan of Livros:

-- EMPRESTIMO DO LIVRO: "MACUNAIMA"
UPDATE tb_livro SET id_status = 2 WHERE id = 200;

-- EMPRESTIMO DO LIVRO: "MEMORIAS POSTUMAS"
UPDATE tb_livro SET id_status = 2 WHERE id = 100;

The attempt to alter the Status of a Livro for something invalid would cause foreign key violation, not allowing a Status invalid of Livro:

UPDATE tb_livro SET id_status = 3 WHERE id = 100;

Exit:

ERROR: insert or update on table "tb_livro" violates foreign key constraint "tb_livro_id_status_fkey" Detail: Key (id_status)=(3) is not present in table "tb_status".

Sqlfiddle: http://sqlfiddle.com/#! 17/b8a20/5

0

You can add a Constraint to check the field values, like this: 

ALTER TABLE nome-da-tabela
   ADD CONSTRAINT check-valores
   CHECK (nome-do-campo= 1 OR nome-do-campo = 2);

Or when creating the table:

CREATE TABLE nome-da-tabela(
    nome-do-campo integer CHECK (nome-do-campo= 1 OR nome-do-campo = 2)
);

  • Um, so inside the creates there’s no way ?

  • has yes, I put in the answer :)

  • This check is a postgres library ?

  • is not an attribute of language sql, works in other databases, as well as other attributes such as not null and unique. Ah, it could also be used like this CHECK (nome-do-campo in (1,2))

  • Thanks there , it worked here ,but if I wanted to define that 1 equals a String has like ?

  • Yes, if your field is string, just quote: '1'

  • Friend, kind of like this :

  • Friend, type like this : 1 = 'Available ' and 2 = 'Borrowed'

  • in that case the check will not help, maybe you have to create a second table and validate with a foreign key, ai in the second table (Type Location for example) you create two fields, code and description, and put the values there. Another option is to display the text using a CASE when selecting the fields: SELECT nome-do-campo,
 CASE WHEN nome-do-campo=1 THEN 'Disponivel '
 WHEN nome-do-campo=2 THEN 'Emprestado'
 END AS Descricao
 FROM nome-da-tabela;

  • I’m new to the site, how do you view pdf here ? ai I would send you the logical template , if you can help me there ?

  • You can put an image in your question, just click on the "edit" link just below the question, but if this specific question has already been resolved, it would be nice to mark the answer as accepted and open a new question with other questions you have

  • friend , I did so, see if you agree ? INSERT INTO exemplary( idobra, numeroexemplary, dataset, situacaxemplar, Description) VALUES (01, 1234,'01/01/2017',01,'Available');

Show 7 more comments

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.