Início / dba / Perguntas / 252247
Accepted
Sarov
Asked: 2019-10-31 11:47:43 +0800 CST

Faz sentido colocar uma restrição exclusiva em um sinalizador de exclusão reversível anulável?

  • 772

Portanto, atualmente temos o seguinte:

MYTABLE
COLUMN: ID (INTEGER Primary key, auto-incrementer)
COLUMN: START (DATE)
COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY)
COLUMN: DELETED (INTEGER)
CHECK: DELETED = 0 OR DELETED = 1

Agora, há o requisito de permitir infinitos registros excluídos, mas permitir apenas um único registro não excluído para cada data + empresa.

Eu sugeri mudar o esquema para:

MYTABLE
COLUMN: ID (INTEGER Primary key, auto-incrementer)
COLUMN: START (DATE)
COLUMN: COMPANYID (INTEGER, Foreign key to COMPANY)
COLUMN: ACTIVE (Nullable INTEGER)
CHECK: ACTIVE = 1 OR ACTIVE IS NULL
UNIQUE: START, COMPANYID, ACTIVE

Enquanto meu colega de trabalho é da opinião de que isso está "indo longe demais com restrições" e que devemos confiar apenas na verificação de exclusividade no aplicativo.

Existe uma prática recomendada geralmente aceita aqui?

database-design unique-constraint

1 respostas

  1. Best Answer

    Você não menciona a qual DBMS você está se referindo, mas sua restrição exclusiva sugerida não funcionará para vários por aí, porque null não é igual a null

    create table t (x int not null, y int, unique(x,y));
insert into t (x) values (1);
insert into t (x) values (1);

Postgres12
Does not violate the constraint

Oracle18c
Violates constraint

Db2 Developer C 11.1
Does not accept nullable columns in unique constraint

Firebird 3.0
Violates constraint

MariaDB 10.4
Does not violate the constraint

MySQL 8.0
Does not violate the constraint

SQLlite 3.27
Does not violate the constraint

SQLServer 2017
Violates constraint

    Testado no DB<>Fiddle

    Então, de um modo geral, sua restrição UNIQUE não funciona. Considere mudar de ATIVO para

    ACTIVE SMALLINT NOT NULL,
CHECK (ACTIVE BETWEEN 0 AND 1),
UNIQUE (START, COMPANYID, ACTIVE)

    Embora eu não esteja convencido de que um determinado ID DA EMPRESA em um START específico possa ser ATIVO e NÃO ATIVO, mas não conheço o seu negócio.

    EDIT: Dadas as novas informações na pergunta, algo assim pode ser possível (não tenho acesso aos documentos do sistema i agora, então não sei se existem colunas geradas ou restrições em expressões)

    create table t 
( a int not null generated always as identity
, x int not null
, y int
, z int not null generated always as ( 
    case when y is null then -1*a else y end 
)
, unique(x,z)
, check (y is null or y = 1)
);

insert into t(x) values (1); -- ok
insert into t(x) values (1); -- ok
insert into t(x,y) values (1,1); -- ok
insert into t(x,y) values (1,1); -- fails because of constraint violation.
    • 2

relate perguntas