As Constraints tem como função primordial, reforçar regras no banco de dados. Existem três tipos de integridades a serem mantidas pelas constraints, são elas:
· Integridade de entidades, que pode ser reforçada através de uma chave primária em uma tabela ou através de um índice do tipo Unique.
· Integridade referencial, mantida através de constraints de FOREIGN KEYS.
· Integridade de domínio, que são regras relacionadas as informações das tabelas, sua nulabilidade e conceituação. Esta integridade é mantida através de check, unique e default constraints.
Podemos criar uma unique constraint junto com a criação de uma tabela assim:
--UNIQUE CONSTRAINTS
CREATE TABLE products
(
codigo INT PRIMARY KEY,
descricao NVARCHAR(150) CONSTRAINT ix_descricao_produto UNIQUE
)
Ou constraints de tabela, e não de coluna, que nos permite referenciar mais de uma coluna:
CREATE TABLE products_2
(
codigo INT PRIMARY KEY,
descricao NVARCHAR(150),
CONSTRAINT ix_descricao_products2 UNIQUE (descricao)
)
CREATE TABLE products_3
(
codigo INT PRIMARY KEY,
descricao NVARCHAR(150)
)
GO
INSERT INTO products_3
(codigo,
descricao)
VALUES (1,
'Rodrigo')
INSERT INTO products_3
(codigo,
descricao)
VALUES (2,
'Rodrigo')
ALTER TABLE products_3
ADD CONSTRAINT ix_descricao_produtos3
UNIQUE (descricao)
/* dá a seguinte mensagem...
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Products_3' and the index name 'ix_descricao_produtos3'. The duplicate key value is (Rodrigo).
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
*/
--REMOVENDO UM DOS DUCPLICADOS...
DELETE FROM products_3
WHERE codigo = 2
--AGORA VAI
ALTER TABLE products_3 ADD CONSTRAINT ix_descricao_produtos3 UNIQUE (descricao)
Podemos criar check constraints junto com o comando CREATE TABLE:
--CHECK CONSTRAINTS
--dentro do create table
CREATE TABLE products_check
(
codigo INT PRIMARY KEY,
descricao NVARCHAR(150),
vlrpreco NUMERIC(18, 2) CHECK (vlrpreco > 0 )
)
GO
INSERT INTO products_check
(codigo,
descricao,
vlrpreco)
VALUES (1,
'Carro',
0)
/*
Dá o seguinte erro
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK__Products___vlrPr__2D27B809". The conflict occurred in database "testdb", table "dbo.Products_Check", column 'vlrPreco'.
The statement has been terminated.
*/
--a inserção só dá erro se o resultado do check for falso, se o resultado for logicamente desconhecido...não há erro
INSERT INTO products_check
(codigo,
descricao,
vlrpreco)
VALUES (1,
'Carro',
NULL)
--1 row(s) affected)
Podemos criar constraints de tabela, referenciando mais de uma coluna:
--table checks permitem que constraints em mais de uma coluna...
CREATE TABLE products_check2
(
codigo INT PRIMARY KEY,
descricao NVARCHAR(150),
fl_ativo BIT,
fl_atualizaestoque BIT,
CONSTRAINT ck_flags CHECK (fl_ativo IS NOT NULL OR fl_atualizaestoque IS
NOT NULL)
)
GO
E que tal adicionar DEFAULT constraints para reforçar regras de negócio? Veja o exemplo com o CREATE TABLE:
-- table check constraint com alter table
CREATE TABLE empregado
(
codigo INT PRIMARY KEY,
nome NVARCHAR(100),
dtcontratacao DATETIME DEFAULT Getdate()
)
GO
Se a tabela já existir, podemos usar o ALTER TABLE:
ALTER TABLE empregado ADD CONSTRAINT ckdataempregado CHECK (dtcontratacao <=
Getdate())
Uma questão que gera diversas discussões, são as check constraints em colunas com valores pré-cadastrados. Se os valores que estão cadastrados violam o check que está sendo adicionado, podemos usar o comando WITH NOCHECK para que a constraint não seja checada no momento da criação. Observe o exemplo:
--check constraints e valores pré-existentes
CREATE TABLE veiculos
(
codigo INT PRIMARY KEY,
placa NVARCHAR(7)
)
GO
INSERT INTO veiculos
(codigo,
placa)
VALUES (1,
'MDT5412')
INSERT INTO veiculos
(codigo,
placa)
VALUES (2,
'MDT5413')
INSERT INTO veiculos
(codigo,
placa)
VALUES (3,
'MDT5413')
INSERT INTO veiculos
(codigo,
placa)
VALUES (4,
NULL)
SELECT *
FROM veiculos
--ISTO DÁ ERRO
ALTER TABLE veiculos ADD CONSTRAINT ck_placa CHECK (placa IS NOT NULL)
/*
Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the CHECK constraint "CK_PLACA". The conflict occurred in database "testdb", table "dbo.Veiculos", column 'placa'.
*/
--ISTO FUNCIONA
ALTER TABLE veiculos WITH NOCHECK ADD CONSTRAINT ck_placa CHECK (placa IS NOT
NULL)
--Command(s) completed successfully.
As NULL e NOT NULL constraints podem ser facilmente implementadas no CREATE TABLE ou através de comandos ALTER TABLE...ALTER COLUMN:
--NULL E NOT NULL CONSTRAINTS
--diretamente no create table
CREATE TABLE departamentos
(
codigo INT PRIMARY KEY,
descricao NVARCHAR(150) NOT NULL,
sigla NVARCHAR(2) NOT NULL,
fl_ativo BIT NULL
)
GO
--com alter table
CREATE TABLE departamentos2
(
codigo INT PRIMARY KEY,
descricao NVARCHAR(150),
sigla NVARCHAR(2),
fl_ativo BIT
)
GO
ALTER TABLE departamentos2 ALTER COLUMN descricao NVARCHAR(150) NOT NULL
A exclusão de constraints, é relativamente simples e pode ser feita através de comandos ALTER TABLE...DROP CONSTRAINTS. Veja o exemplo:
--default constraints
--no create table
CREATE TABLE fornecedores
(
codigo INT PRIMARY KEY,
descricao NVARCHAR(150) DEFAULT ('Não preenchido'),
sigla NVARCHAR(2),
fl_ativo BIT DEFAULT (1)
)
GO
--alter table
ALTER TABLE fornecedores ADD DEFAULT ('NN') FOR sigla
--PARA DROPAR UMA CONSTRAINT...
ALTER TABLE fornecedores
DROP CONSTRAINT df__fornecedo__sigla__4bac3f29
Podemos desabilitar uma constraint, com o comando ALTER TABLE...NOCHECK. Veja o exemplo:
--desabilitando uma constraint pelo nome
ALTER TABLE dbo.products_check
NOCHECK CONSTRAINT ck__products___vlrpr__2d27b809
--desabilitando todas
ALTER TABLE dbo.products_check NOCHECK CONSTRAINT ALL
--re-habilitando tudo...
ALTER TABLE dbo.products_check CHECK CONSTRAINT ALL
--re-habilitando pelo nome
ALTER TABLE dbo.products_check
CHECK CONSTRAINT ck__products___vlrpr__2d27b809
E se ao re-habilitar uma constraint quisermos saber se algum registro viola esta regra? Veja através do exemplo, como utilizar o comando DBCC CHECKCONSTRAINTS.
--checkando constraints forçadamente
CREATE TABLE alunos
(
nome VARCHAR(100),
dtmatricula DATETIME CHECK (dtmatricula<=Getdate())
)
GO
ALTER TABLE alunos NOCHECK CONSTRAINT ALL
INSERT INTO alunos
(nome,
dtmatricula)
VALUES ('Rodrigo',
Getdate() + 1)
ALTER TABLE alunos CHECK CONSTRAINT ALL
DBCC checkconstraints(alunos)
/*
Table Constraint Where
--------------- --------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[dbo].[Alunos] [CK__Alunos__dtmatric__4F7CD00D] [dtmatricula] = '2010-07-30 15:20:14.030'
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/
Se quiser checar as constraints de todo o banco de dados, utilize o comando DBCC CHECKCONSTRAINTS omitindo o nome da tabela.
Espero que tenham aproveitado o post e até a próxima!
Nenhum comentário:
Postar um comentário
Leave your comment here!