Answer Posted / aryarokz

Data integrity allows to define certain data quality
requirements that the data in the database needs to meet. If
a user tries to insert data that doesn't meet these
requirements, Oracle will not allow so.
Constraint types
There are five integrity constraints in Oracle.
Not Null
A column in a table can be specified not null. It's not
possible to insert a null in such a column. The default is
null. So, in the following create table statement, a null
can be inserted into the column named c.

create table ri_not_null (
a number not null,
b number null,
c number
);

insert into ri_not_null values ( 1, null, null);
insert into ri_not_null values ( 2, 3, 4);
insert into ri_not_null values (null, 5, 6);

The first to records can be inserted, the third cannot,
throwing a ORA-01400: cannot insert NULL into
("RENE"."RI_NOT_NULL"."A").
The not null/null constraint can be altered with

alter table ri_not_null modify a null;

After this modification, the column a can contain null values.
Unique Key
The unique constraint doesn't allow duplicate values in a
column. If the unique constraint encompasses two or more
columns, no two equal combinations are allowed.

create table ri_unique (
a number unique,
b number
);

However, if a column is not explicitely defined as not null,
nulls can be inserted multiple times:

insert into ri_unique values (4, 5);
insert into ri_unique values (2, 1);
insert into ri_unique values (9, 8);
insert into ri_unique values (6, 9);
insert into ri_unique values (null,9);
insert into ri_unique values (null,9);

Now: trying to insert the number 2 again into a:

insert into ri_unique values (2,7);

This statement issues a ORA-00001: unique constraint
(RENE.SYS_C001463 violated). Every constraint, by the way,
has a name. In this case, the name is: RENE.SYS_C001463.
In order to remove that constraint, an alter table ... drop
constraint ... is needed:

alter table ri_unique drop constraint sys_c001463;

Of course, it is also possible to add a unique constraint on
an existing table:

alter table ri_unique add constraint uq_ri_b unique (b);

A unique constraint can be extended over multiple columns:

create table ri_3 (
a number,
b number,
c number,
unique (a,b)
);

It is possible to name the constraint. The following example
creates a unique constraint on the columns a and b and names
the constraint uq_ri_3.

create table ri_3 (
a number,
b number,
c number,
constraint uq_ri_3 unique (a,b)
);

Primary Key
On a technical level, a primary key combines a unique and a
not null constraint. Additionally, a table can have at most
one primary key. After creating a primary key, it can be
referenced by a foreign key.

create table ri_primary_key (
a number primary key,
b number
);

Primary keys can explicitely be named. The following create
table statement creates a table with a primary key whose
name is pk_name.

create table ri_primary_key_1 (
a number,
b number,
c number,
constraint pk_name primary key (a, b)
);

Foreign Key
A foreign key constraint (also called referential integrity
constraint) on a column ensures that the value in that
column is found in the primary key of another table.
If a table has a foreign key that references a table, that
referenced table can be dropped with a drop table .. cascade
constraints.
It is not possible to establish a foreign key on a global
temporary table. If tried, Oracle issues a ORA-14455:
attempt to create referential integrity constraint on
temporary table.
Check
A check constraint allows to state a minimum requirement for
the value in a column. If more complicated requirements are
desired, an insert trigger must be used.
The following table allows only numbers that are between 0
and 100 in the column a;

create table ri_check_1 (
a number check (a between 0 and 100),
b number
);

Check constraints can be added after a table had been created:

alter table ri_check_1
add constraint ch_b check (b > 50);

It is also possible to state a check constraint that check
the value of more than one column. The following example
makes sure that the value of begin_ is smaller than the
value of end_.

create table ri_check_2 (
begin_ number,
end_ number,
value_ number,
check (begin_ < end_)
);

What is pl sql record in oracle?

971

---

What are properties of the transaction?

958

---

Which is better cte or subquery?

971

---

How can I delete duplicate rows?

966

---

what are the different tables present in mysql, which type of table is generated when we are creating a table in the following syntax: create table employee (eno int(2),ename varchar(10)) ? : Sql dba

990

---

Explain what is table in a database?

1009

---

Why do we need databases?

987

---

Why are cursors used?

1059

---

Explain what is a database?

1039

---

what does myisamchk do? : Sql dba

964

---

Mention what plvcmt and plvrb does in pl/sql?

1062

---

Explain the difference between triggers and constraints?

921

---

What is trigger price?

926

---

Is record in oracle pl sql?

904

---

What is the use of function "module procedure" in pl/sql?

1136

---