Assegurar a integridade dos dados com restrições

{númeroDeMinutos} minutos

Restrições e objetos de sequência são escolhas de design que previnem problemas de dados antes que ocorram. Uma restrição de chave estrangeira em falta significa que registos órfãos podem já existir na sua base de dados. Adicionar objetos de sequência posteriormente para substituir colunas do tipo identidade requer alterações em todas as aplicações. O código da aplicação pode validar dados, mas os utilizadores podem contornar a validação através de importações em massa, consultas diretas ou novas aplicações que saltam a validação.

As restrições da base de dados impõem regras ao nível do motor, por isso aplicam-se sempre. As decisões que toma durante o design, como quais regras aplicar na base de dados e se deve usar colunas ou sequências de identidade, afetam a qualidade dos seus dados durante a vida útil da sua aplicação.

Compreenda quando usar restrições

Os problemas de qualidade dos dados são dispendiosos. A má qualidade dos dados leva a decisões empresariais incorretas, integrações falhadas e violações de conformidade. Ao contrário da validação ao nível da aplicação, que pode ser inconsistente entre diferentes aplicações ao aceder à mesma base de dados, as restrições impõem regras ao nível do motor da base de dados, onde não podem ser contornadas por código de aplicação, consultas ad hoc, scripts SQL diretos ou importações em massa. Cada INSERT, UPDATE e DELETE deve satisfazer todas as restrições definidas antes de o motor da base de dados efetuar a alteração.

Aplicar restrições de base de dados

As restrições previnem problemas de qualidade dos dados antes de corromperem a sua base de dados. A tabela seguinte mostra como cada tipo de restrição resolve questões específicas de integridade dos dados:

Problema	Restrição	Example
Registos órfãos	CHAVE ESTRANGEIRA	Previne encomendas sem clientes válidos
Dados duplicados	UNIQUE	Impede registos duplicados por email
Dados inválidos	CHECK	Rejeita preços negativos ou datas de nascimento futuras
Dados críticos em falta	NÃO NULO	Previne registos incompletos
Inconsistência referencial	CHAVE ESTRANGEIRA	Mantém a integridade dos dados entre tabelas

Considere uma empresa de retalho que não definiu uma restrição única na sua coluna de emails de clientes. Com o tempo, os mesmos clientes foram registados várias vezes com endereços de email idênticos. Quando o marketing enviava campanhas promocionais, alguns clientes recebiam três cópias do mesmo email, aumentando os custos e prejudicando a confiança dos clientes. Adicionar UNIQUE (EmailAddress) à definição da tabela teria impedido que estes duplicados fossem alguma vez inseridos.

As restrições impõem regras ao nível do motor da base de dados, garantindo a qualidade dos dados independentemente de como os dados entram no sistema. A validação da aplicação pode ser contornada, varia consoante a aplicação e é mais difícil de manter. As restrições da base de dados são sempre aplicadas, centralizadas e fornecem uma fonte única de verdade.

As restrições garantem a qualidade e consistência dos dados ao nível da base de dados.

Usar restrições de chave primária

As principais restrições de chave garantem dados únicos e garantem a integridade da entidade. Quando especifica uma restrição de chave primária, o Motor de Base de Dados cria automaticamente um índice único para as colunas principais primárias. Uma tabela pode conter apenas uma restrição de chave primária, e todas as colunas definidas dentro de uma restrição de chave primária devem ser definidas como NOT NULL.

Pode criar uma chave primária usando a PRIMARY KEY restrição. Eis um exemplo:

CREATE TABLE Customer (
    CustomerID INT PRIMARY KEY IDENTITY(1,1),
    EmailAddress NVARCHAR(100) NOT NULL
);

Usar restrições de chave estrangeira

As restrições de chave estrangeira impõem a integridade referencial ao controlar os dados que podem ser armazenados na tabela de chave estrangeira. Uma restrição de chave estrangeira impede alterações aos dados na tabela de chaves primárias se essas alterações invalidarem a ligação aos dados na tabela de chave estrangeira.

Pode definir ações referenciais em cascata como CASCADE, SET NULL, ou SET DEFAULT especificar o que acontece quando um utilizador tenta eliminar ou atualizar uma chave para a qual as chaves estrangeiras existentes apontam. Embora não seja necessário fazer manualmente a criação de um índice nas colunas de chave estrangeira, é frequentemente útil porque as colunas de chave estrangeira são frequentemente usadas nos critérios de associação.

Pode criar uma chave estrangeira usando a FOREIGN KEY restrição com uma REFERENCES cláusula. Eis um exemplo:

CREATE TABLE Order (
    OrderID INT PRIMARY KEY IDENTITY,
    CustomerID INT NOT NULL,
    OrderDate DATETIME2,
    FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
);

Use restrições únicas

Restrições únicas garantem que não sejam inseridos valores duplicados em colunas específicas que não participem numa chave primária. Ao contrário das restrições PRIMARY KEY, as restrições UNIQUE permitem o valor NULL. No entanto, como acontece com qualquer valor que participa de uma restrição de UNIQUE, apenas um valor nulo é permitido por coluna. O Motor de Base de Dados cria automaticamente um índice único não agrupado para impor o requisito de unicidade.

Pode criar uma restrição única usando a UNIQUE palavra-chave. Eis um exemplo:

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    SKU NVARCHAR(50) UNIQUE,
    ProductName NVARCHAR(100)
);

Usar restrições de verificação

As restrições de verificação reforçam a integridade do domínio ao limitar os valores aceites por uma ou mais colunas. Pode criar uma CHECK restrição com qualquer expressão lógica que retorne TRUE ou FALSE baseada em operadores lógicos. Pode aplicar múltiplas CHECK restrições a uma única coluna ou aplicar uma única CHECK restrição a várias colunas.

Como os valores nulos são avaliados como UNKNOWN, a sua presença nas expressões pode anular uma restrição. Por exemplo, uma restrição MyColumn = 10 numa coluna ainda permite INT ser inserida porque NULL não avalia a NULLFALSE .

Pode criar uma restrição CHECK usando a CHECK palavra-chave com uma expressão lógica. Eis um exemplo:

CREATE TABLE Employee (
    EmployeeID INT PRIMARY KEY,
    HireDate DATE,
    Salary DECIMAL(10,2),
    CHECK (Salary >= 20000),
    CHECK (HireDate <= GETDATE())
);

Usar restrições padrão

As restrições padrão fornecem valores padrão quando nenhum valor é especificado durante as INSERT operações. Ao trabalhar com projetos de bases de dados, recomenda-se criar restrições com nomes explícitos em vez de permitir nomes gerados pelo sistema, que diferem entre ambientes.

Pode criar uma restrição PADRÃO usando a DEFAULT palavra-chave. Eis um exemplo:

CREATE TABLE Activity (
    ActivityID INT PRIMARY KEY IDENTITY,
    Description NVARCHAR(200),
    CreatedDate DATETIME2 CONSTRAINT DF_Activity_CreatedDate DEFAULT GETUTCDATE(),
    IsActive BIT CONSTRAINT DF_Activity_IsActive DEFAULT 1
);

Usar objetos de sequência

Um objeto de sequência é um objeto definido pelo utilizador por esquema que gera uma sequência de valores numéricos de acordo com a especificação com que a sequência foi criada. Ao contrário das colunas identidade, as sequências não estão associadas a tabelas específicas. As aplicações referem-se a um objeto de sequência para recuperar o seu próximo valor, e a relação entre sequências e tabelas é controlada pela aplicação.

As colunas de identidade funcionam bem quando precisas de numeração automática para uma única tabela. No entanto, estão limitados a essa única mesa. Não podes partilhar os números em várias tabelas, obter o valor seguinte antes de inserir uma linha, ou reiniciar facilmente o contador. Os objetos de sequência resolvem estes problemas gerando números independentemente de qualquer tabela.

Compreenda quando usar sequências

Use sequências em vez de colunas de identidade nos seguintes cenários:

Série numérica partilhada - A aplicação requer a partilha de uma única série de números entre múltiplas tabelas ou várias colunas dentro de uma tabela.
Ciclagem da série numérica - A aplicação deve reiniciar a série numérica quando um número especificado é atingido. Por exemplo, depois de atribuir valores de 1 a 10, o aplicativo começa a atribuir valores de 1 a 10 novamente.
Valores de sequência ordenados - A aplicação requer que os valores de sequência sejam ordenados por outro campo. A NEXT VALUE FOR função pode aplicar a OVER cláusula, o que garante que os valores devolvidos são gerados na ordem da ORDER BY cláusula.
Reserve vários números - Uma aplicação precisa de reservar vários números sequenciais. Solicitar valores de identidade pode resultar em lacunas se outros processos recebessem números simultaneamente. Chamar sp_sequence_get_range recupera vários números da sequência simultaneamente.
Especificação alterável - Precisa de alterar a especificação da sequência, como o valor de incremento, após a criação.

Os objetos de sequência podem ser mais flexíveis do que as colunas de identidade.

Característica	Sequence	Identidade
Ligado à tabela	Não	Yes
Partilhado entre tabelas ou colunas	Yes	Não
Obtenha o próximo valor antes da operação de inserção	Yes	Não
Valores mínimos/máximos personalizados	Yes	Limitada
Recuperar vários números ao mesmo tempo	Yes	Não
Alternar/reiniciar no número definido	Yes	Não
Ordenar valores por outro campo	Yes	Não
Alterar incremento após a criação	Yes	Não

Use uma coluna de identidade quando precisar de uma chave primária simples com autoincremento para uma única tabela e não precisar de partilhar a mesma série numérica em várias tabelas ou recuperar o valor seguinte antes de inserir a linha.

Use uma sequência quando a sua aplicação exigir um número antes de a inserção ser feita, precisar de partilhar uma série única entre várias tabelas, tiver de reiniciar uma série quando for atingido um número especificado, ou precisar de reservar vários números sequenciais ao mesmo tempo.

Compreender as limitações da sequência

Ao contrário das colunas de identidade, os valores de sequência não são automaticamente protegidos após a inserção numa tabela. Além disso, a unicidade não é automaticamente aplicada para valores de sequência. Se os valores de sequência numa tabela tiverem de ser únicos, crie uma restrição única na coluna.

Os números de sequência são gerados fora do escopo da transação atual. Eles são consumidos independentemente de a transação que usa o número de sequência ser confirmada ou revertida.

Podes criar um objeto de sequência usando a CREATE SEQUENCE instrução com parâmetros opcionais para início, incremento e alcance. Eis um exemplo:

-- Create sequence
CREATE SEQUENCE OrderNumber 
    START WITH 1000
    INCREMENT BY 1
    MINVALUE 1000
    MAXVALUE 999999
    NO CYCLE;

-- Use sequence in INSERT with NEXT VALUE FOR function
INSERT INTO Order (OrderID, CustomerID, OrderNumber, OrderDate)
VALUES (1, 100, NEXT VALUE FOR OrderNumber, GETDATE());

-- Get next value before INSERT
DECLARE @NextOrderNum INT = NEXT VALUE FOR OrderNumber;
SELECT @NextOrderNum;

-- Get multiple sequence numbers at once for batch processing
DECLARE @FirstSeq INT, @LastSeq INT;
EXEC sp_sequence_get_range 
    @sequence_name = N'OrderNumber',
    @range_size = 100,
    @range_first_value = @FirstSeq OUTPUT,
    @range_last_value = @LastSeq OUTPUT;

-- Reset sequence
ALTER SEQUENCE OrderNumber RESTART WITH 1000;

Este exemplo cria uma sequência chamada OrderNumber que começa em 1000, incrementa em 1 e para em 999999 sem voltar atrás. A função NEXT VALUE FOR recupera o próximo número disponível, seja diretamente durante uma instrução INSERT ou atribuído a uma variável antes da inserção, quando é necessário que a aplicação se refira ao valor primeiro. Para operações em lote que requerem múltiplos números sequenciais ao mesmo tempo, sp_sequence_get_range reserva-se um bloco de 100 números, retornando o primeiro e o último valores do intervalo. A ALTER SEQUENCE instrução reinicia a sequência para 1000 quando necessário.

As restrições são decisões arquitetónicas que previnem problemas antes de ocorrerem. Uma restrição em falta CHECK permite que dados inválidos corrompam silenciosamente a sua base de dados. Escolher colunas de identidade quando precisa de numeração entre tabelas obriga a soluções alternativas ao nível da aplicação. As restrições definidas ao nível da base de dados protegem a qualidade dos dados independentemente da aplicação, ferramenta ou script que acede à sua base de dados. Estas decisões moldam as garantias de integridade dos seus dados durante toda a vida útil da sua aplicação.

Comentários

Esta página foi útil?