Uma constraint ou restrição é uma regra em uma tabela que restringe os valores que podem ser adicionados às colunas de uma tabela. Observe que não é um objeto de banco de dados, mas está listado no dicionário de dados e pode ser nomeado com as mesmas regras de nomenclatura de um objeto. Você já examinou os diferentes tipos de restrições: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, e CHECK. Se uma instrução SQL tentar adicionar, modificar ou excluir uma linha de uma tabela e violar uma restrição, a instrução SQL inteira falhará com um erro de execução.
Criando constraint na criação de uma tabela
Podemos criar constraints para objetos e especialmente para tabelas onde não utilizamos o comando CREATE CONSTRAINT mas sim como parte do comando CREATE TABLE ou ALTER TABLE. Quando criamos uma constraint podemos opcionalmente definir um nome para ela ou deixar que o sistema defina automaticamente. A criação de certas restrições – PRIMARY KEY, UNIQUE e FOREIGN KEY – acionará automaticamente a criação de um objeto de índice correspondente com o mesmo nome.
CREATE TABLE positions (
position_id NUMBER ,
position VARCHAR2(20) ,
exempt CHAR(1) ,
CONSTRAINT positions_pk PRIMARY KEY (position_id)
) ;
No exemplo anterior, criamos uma TABELA chamada POSITIONS, que consiste em três colunas, POSITION_ID, POSITION e EXEMPT. Após a definição da coluna EXEMPT, este exemplo em particular mostra uma linha de código adicional para criar um CONSTRAINT. (Existem outros formatos para criar restrições, como você verá em breve.) Neste exemplo, estamos optando por nomear CONSTRAINT, algo que não precisamos necessariamente fazer. O nome que estamos atribuindo a este CONSTRAINT é POSITIONS_PK. Especificamos que este CONSTRAINT é do tipo PRIMARY KEY e estamos aplicando o CONSTRAINT à coluna nesta tabela chamada POSITION_ID, que definimos primeiro.
Criando Constraints na declaração CREATE TABLE
Há duas maneiras pelas quais um CONSTRAINT pode ser criado no momento da criação de TABLE: na linha e fora da linha.
CREATE TABLE: restrições em linha
Aqui está um exemplo de como criar uma restrição PRIMARY KEY na linha.
CREATE TABLE PORTS(PORT_ID INTEGER PRIMARY KEY, PORT_NAME VARCHAR2(20));
Neste exemplo, criamos uma restrição PRIMARY KEY na coluna PORT_ID. A restrição em PORT_ID é especificada como parte da declaração PORT_ID. Está “alinhado” com a coluna na qual a restrição é aplicada. Quando digo “em linha”, quero dizer que, depois de declararmos uma coluna à qual queremos aplicar uma restrição, incluiremos a especificação da restrição (neste caso, para uma chave primária) antes da vírgula que separa a especificação da coluna (PORT_ID neste exemplo) da próxima especificação de coluna, que é PORT_NAME. O fato de o exemplo incluir as palavras PRIMARY KEY na mesma linha que PORT_ID é incidental e desnecessário para torná-lo um formato “em linha”. Neste exemplo, omitimos a inclusão de um nome especificado para a restrição, o que faz com que o banco de dados Oracle gere um nome de restrição automaticamente, no formato SYS_Cn. Aqui está um exemplo:
SYS_C009982
Vamos identificar o nome atribuído ao sistema ao consultar o dicionário de dados. Executamos a seguinte consulta para ver o nome criado:
SELECT CONSTRAINT_NAME, COLUMN_NAME, TABLE_NAME
FROM USER_CONS_COLUMNS
WHERE TABLE_NAME = 'PORTS';
Como alternativa, você pode opcionalmente especificar o nome da restrição no momento da criação, precedendo as palavras reservadas PRIMARY KEY com a palavra reservada CONSTRAINT, seguida de um nome criado de acordo com as regras de nomeação de objetos de banco de dados, como este:
CREATE TABLE PORTS(PORT_ID INTEGER CONSTRAINT PORT_ID_PK PRIMARY KEY,
PORT_NAME VARCHAR2(20)
);
Essas duas abordagens são chamadas de restrições em linha, pois nos dois exemplos a declaração da restrição é incluída na definição da coluna, antes da vírgula que encerra a especificação desta coluna e permite uma especificação subsequente da coluna. Aqui está outro exemplo de restrições em linha. Este exemplo cria uma tabela com uma restrição NOT NULL:
CREATE TABLE VENDORS(VENDOR_ID NUMBER,
VENDOR_NAME VARCHAR2(20),
STATUS NUMBER(1) NOT NULL,
CATEGORY VARCHAR2(5));
O resultado dessa restrição é garantir que um valor para STATUS seja incluído em cada linha inserida em VENDORS. O valor pode ser zero ou qualquer outro dígito único, mas deve ser fornecido – não pode ser deixado de fora. Não pode ser desconhecido para o banco de dados. Em outras palavras, não pode ser NULL. Aqui está a mesma tabela com um nome atribuído à restrição:
CREATE TABLE VENDORS(VENDOR_ID NUMBER,
VENDOR_NAME VARCHAR2(20),
STATUS NUMBER(1) CONSTRAINT STATUS_NN NOT NULL,
CATEGORY VARCHAR2(5));
Você pode combinar várias declarações de restrição em uma única instrução CREATE TABLE, assim:
CREATE TABLE VENDORS(VENDOR_ID NUMBER PRIMARY KEY,
VENDOR_NAME VARCHAR2(20),
STATUS NUMBER(1) CONSTRAINT STATUS_NN NOT NULL,
CATEGORY VARCHAR2(5));
Cada especificação de restrição é tratada como uma cláusula independente da instrução CREATE. Você pode misturar e combinar tipos de definição; você pode optar por nomear a restrição em algumas cláusulas e não em outras. Você também pode misturar e combinar restrições em linha com restrições fora de linha, para as quais voltamos nossa atenção a seguir.
CREATE TABLE: restrições fora de linha
Além das restrições em linha, você pode definir opcionalmente uma restrição em uma instrução CREATE TABLE após as especificações da coluna. Aqui está um exemplo de uma CHAVE PRIMÁRIA definida com a sintaxe fora de linha, neste exemplo na linha 4:
CREATE TABLE PORTS(
PORT_ID INTEGER PRIMARY KEY,
PORT_NAME VARCHAR2(20),
PRIMARY KEY(PORT_ID)
);
Depois que a coluna final é definida para a tabela, incluímos uma vírgula, seguida pelas palavras reservadas PRIMARY KEY. Observe que a sintaxe fora de linha exige que você indique qual coluna (ou colunas) é afetada pela restrição. Como você não está “alinhado” com a especificação da coluna, a instrução não pode saber qual coluna você pretende restringir, a menos que você a indique especificamente na cláusula. Aqui está um exemplo fora de linha que nomeia a restrição:
create table ports(
port_id number,
port_name varchar2(20),
constraint port_id_pk primary key(port_id));
Este exemplo fornece à restrição um nome que escolhemos. Como é o caso das restrições em linha, qualquer restrição fora da linha na qual você não fornece um nome fará com que o banco de dados Oracle gera automaticamente um nome para a restrição.
Alterando nome da constraint
Ao criar uma constraint ou alterar uma coluna criando uma constraint e não informando seu nome o oracle irá criar automaticamente o nome da constraint. Para renomear o nome de uma constraint usamos a seguinte sintaxe:
alter table ports rename constraint SYS_C0011505 to PORT_ID_PK;
Para descobrir o nome da constraint temos duas views do dicionário de dados que mostram informações sobre constraints: USER_CONSTRAINTS, USER_CONS_COLUMNS. Nomeando uma constraint usando o ALTER TABLE:
alter table teste add constraint nome_da_constraint primary key(id);
Constraint NOT NULL
A constraint NOT NULL só é possível sua criação na forma inline. Vamos ver as duas únicas formas de criar/alterar uma constraint NOT NULL:
CREATE TABLE TESTE_NULO(COLUNA INT NOT NULL);
ALTER TABLE TESTE_NULO MODIFY COLUNA NOT NULL;
Outras formas que não sejam as acima não são válidas.
UNIQUE
A constraint unique serve para proibir inserção de dados repetidos, nulos podem ser inseridos a vontade, no entanto, se informado um valor esse valor não pode ser inserido novamente, a constraint unique pode ser combinado para duas ou mais colunas, a regra é a mesma não pode ter valores repetidos, exemplo:
create table x(colx int, coly int, unique(colx, coly));
insert into x values(1,1);
insert into x values(1,null);
insert into x values(1,null);
Na linha 4 acima teremos um erro, pois não podemos ter valores repetidos, mesmo uma coluna sendo valor nulo. Vejamos um exemplo de uma coluna com a constraint unique:
create table y(colx int unique);
insert into y values(1);
insert into y values(null);
insert into y values(null);
select * from y;
No exemplo acima todas instruções foram executadas com sucesso, e ao executar o select teremos os valores (1, null, null).
PRIMARY KEY
A constraint primary key só pode haver uma constraint na tabela e ela é a combinação de unique com not null. Vejamos exemplos que podemos executar:
create table y(colx int primary key);
create table x(colx int, primary key(colx));
create table z(colx int, constraint colx_pk primary key(colx));
create table w(colx int);
alter table w add primary key(colx);
create table a(c1 int, c2 int, primary key(c1, c2));
create table b(c1 int, c2 int, constraint duplo_pk primary key(c1, c2));
create table c(c1 int, c2 int);
alter table c add primary key(c1, c2);
create table d(c1 int, c2 int);
alter table d add constraint duplo_pk2 primary key(c1, c2);
create table e(c1 int, c2 int);
alter table e modify c1 primary key;
select * from user_constraints where table_name = 'E' --SYS_C0019988942
FOREIGN KEY
Uma restrição FOREIGN KEY ou CHAVE ESTRANGEIRA se aplica a uma ou mais colunas em uma tabela específica e trabalha em conjunto com a restrição PRIMARY KEY da tabela referida. A restrição FOREIGN KEY faz o seguinte:
Identifica uma ou mais colunas na tabela atual; vamos chamar isso de tabela filho. Para cada uma dessas colunas, ele também identifica uma ou mais colunas correspondentes em uma segunda tabela; vamos chamar isso de tabela pai. Ele garante que a tabela pai já tenha uma restrição PRIMARY KEY ou UNIQUE nas colunas correspondentes. Em seguida, assegura que quaisquer valores futuros adicionados às colunas restritas FOREIGN KEY da tabela filha já estejam armazenados nas colunas correspondentes da tabela pai.
Quando trabalhamos com FOREIGN KEY temos uma cláusula adicional, ON DELETE, serve para ao remover uma linha da tabela pai irá remover ou setar como NULL no campo da tabela filha, para setar null use ON DELETE SET NULL e para remover a linha da tabela filha use ON DELETE CASCADE. Veremos alguns exemplos:
create table pai(id int unique);
insert into pai values(1);
create table filha(son int references pai(id));
insert into filha values(1);
delete pai;
integrity constraint (SQL_TGHVRBSVGUAHIPYGPEFCASMBO.SYS_C0020154711) violated -
child record found ORA-06512: at "SYS.DBMS_SQL", line 1721
--Não é possível deletar a tabela pai pois há registros na tabela filha
--vamos então incluir uma constraint na tabela filha com a cláusula on delete
SELECT CONSTRAINT_NAME, COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME = 'FILHA';
CONSTRAINT_NAME=SYS_C0020154711
COLUMN_NAME=SON
ALTER TABLE FILHA DROP CONSTRAINT SYS_C0020154711;
ALTER TABLE FILHA ADD FOREIGN KEY (SON) REFERENCES PAI(ID) ON DELETE SET NULL;
SELECT * FROM PAI; --1
SELECT * FROM FILHA; --1
DELETE PAI;
SELECT * FROM FILHA; --NULL
Podemos no exemplo acima ter substituído ON DELETE SET NULL por ON DELETE CASCADE, a diferença é que ao invés de termos um valor null na tabela filha teremos a linha na tabela filha que possui o valor 1 deletado, caso tivesse mais de uma linha com o valor 1 todas seriam deletadas, no caso do SET NULL todas teriam seus valores substituídos para null.
CHECK
A constraint CHECK serve para garantirmos que ao inserir um valor em um campo de uma determinada tabela esse valor respeite o valores pré determinados dessa constraint. Vejamos exemplos:
create table t1(id int check (id between 1 and 5));
insert into t1 values(1); --1 row(s) inserted.
insert into t1 values(6); --erro pois não respeita a constraint que permite apenas números de 1 a 5
insert into t1 values(null); --1 row(s) inserted.
Embora possamos registrar dados nulos na coluna id da tabela t1, só podemos informar valores que compreendem 1 a 5. Qualquer expressão SQL válida pode ser usada em uma restrição CHECK, com algumas limitações. A condição CHECK não pode incluir as seguintes referências:
- Colunas em outras tabelas (observe que outras colunas na mesma tabela são aceitas)
- Pseudocolunas CURRVAL, NEXTVAL, LEVEL ou ROWNUM
- Subconsultas e expressões de subconsultas escalares
- Funções definidas pelo usuário
- Certas funções cujo valor não pode ser conhecido no momento da chamada: SYSDATE, SYSTIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP, SESSIONTIMEZONE, UID, USER e USERENV
Para que uma linha de dados seja aceita como entrada para uma determinada tabela, qualquer restrição CHECK presente deve ser avaliada como TRUE ou desconhecida, devido a um NULL.
Não são permitidos criar constraints nesses tipos de dados: TIMESTAMP WITH TIME ZONE, BLOB e CLOB.
Referência
OCA Oracle Database SQL Exam Guide (1Z0-071). Páginas 115 a 134; 179.
desenvoracle.home.blog 