Checking column padding with CHECK

0

I'm creating the DB for my TCC and the table Clients are:

create table clients(
id serial primary key,
name varchar(255),
cpf varchar(11),
cnpj varchar(14),
....

I thought of doing a CHECK limiting the OR fill of the CPF or CNPJ, in case the 2 can never be empty .. Obviously I'll do it in the back, but I wanted a security in the DB too, how do I do this?

Thank you

    
asked by anonymous 17.08.2017 / 00:58

2 answers

1

Hello,

In your case, I know a solution with TRIGGER, here's an example:

CREATE TABLE foo (
  FieldA INT,
  FieldB INT
);

DELIMITER //
CREATE TRIGGER InsertFieldABNotNull BEFORE INSERT ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
CREATE TRIGGER UpdateFieldABNotNull BEFORE UPDATE ON foo
FOR EACH ROW BEGIN
  IF (NEW.FieldA IS NULL AND NEW.FieldB IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'FieldA\' and \'FieldB\' cannot both be null';
  END IF;
END//
DELIMITER ;

INSERT INTO foo (FieldA, FieldB) VALUES (NULL, 10); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (10, NULL); -- OK
INSERT INTO foo (FieldA, FieldB) VALUES (NULL, NULL); -- gives error
UPDATE foo SET FieldA = NULL; -- gives error

Fiddle with the example: link

For PostgreSQL just create a Trigger that returns NULL, eg:

create table stuff (
  stuff_id int primary key,
  thing text
);

create or replace function stuff_inserting() returns trigger language plpgsql as $$
begin

  return null;

end $$;

insert into stuff values (1, 'asdf');

select * from stuff; /* returns 1 row */

create trigger inserting before insert on stuff for each row execute procedure stuff_inserting();

insert into stuff values (2, 'fdsa');

select * from stuff; /* still returns only 1 row */
    
17.08.2017 / 01:34
1

On the answer of my problem, I managed to solve with the help of Diego Marques:

CREATE TABLE usuario(
id serial, 
cpf varchar(20),
cnpj varchar(20)
);
create or replace function cpfcnpjVerification() returns trigger language plpgsql as $$
begin
IF (NEW.cpf IS NULL AND NEW.cnpj IS NULL) THEN
  RAISE SQLSTATE '09000'
  USING MESSAGE = 'Column CPF and CNPJ cannot both be null';
END IF;

return NEW;
end $$;

create trigger InsertCpfCnpjNotNull before insert on usuario 
for each row execute procedure cpfcnpjVerification();
INSERT INTO usuario(cpf, cnpj) VALUES('22','33') -- normal
INSERT INTO usuario(cpf, cnpj) VALUES(NULL,'44') -- normal
INSERT INTO usuario(cpf, cnpj) VALUES('55',NULL) -- normal
INSERT INTO usuario(cpf, cnpj) VALUES(NULL,NULL) -- ERROR: Column CPF and CNPJ cannot both be null
                                                 --SQL state: 09000
                                                 --Context: PL/pgSQL function cpfcnpjverification() line 4 at RAISE

One question I had was regarding SQL STATE, which to use, but based on Postgres I chose the 09000 although I do not think it makes that much difference to me.

Thank you

    
17.08.2017 / 18:58