Constraints (Oracle)

TODO

Drop child table

create table tq84_table_parent (
   id  number primary key,
   tx  varchar2(10)
);

create table tq84_table_child  (
   id_parent references tq84_table_parent,
   tx  varchar2(10)
);

select count(*) || ' constraints found' from user_constraints where table_name = 'TQ84_TABLE_CHILD';

drop table tq84_table_parent cascade constraints purge;

desc tq84_table_child;

select count(*) || ' constraints found' from user_constraints where table_name = 'TQ84_TABLE_CHILD';


drop table tq84_table_child purge;

Github repository Oracle-Patterns, path: /DatabaseObjects/Tables/IntegrityConstraints/drop_child.sql

See also the cascade constraints clause of the drop table statement.

NOT NULL, not unique

A not null constraints prevents a column value from being null.

create table tq84_not_null_non_unique (
  col_1     number,
  col_2     varchar2(10),
  col_3     varchar2(10),
  --
  constraint tq84_not_null_non_unique_uq unique (col_1, col_2)
);


insert into tq84_not_null_non_unique values (   1, 'one' , 'uno'   );
insert into tq84_not_null_non_unique values (null, 'null', 'niente');
insert into tq84_not_null_non_unique values (   0,  null , 'niente');
insert into tq84_not_null_non_unique values (   1,  null , 'foo'   );
insert into tq84_not_null_non_unique values (null, 'NULL', 'bar'   );
insert into tq84_not_null_non_unique values (null,  null , 'baz'   );
insert into tq84_not_null_non_unique values (null,  null , 'baz 2' );



drop table tq84_not_null_non_unique purge;

Github repository Oracle-Patterns, path: /DatabaseObjects/Tables/IntegrityConstraints/not_null_non_unique.sql

Unique	Makes sure that there are no duplicate values. Unlike primary keys, a unique constraint allows `null` values to be inserted.
Check	The check constraint makes sure that the values of a column adheres to the conditions set forth in the constraint.
Not null	The not `null` constraint is a special check conststraint that prevents a value from being `null`.
Primary key	Is a combination of a `not null` and `unique` constraints
Foreign key	Makes sure that the values are present in a corresponding primary key