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_)
);

If the application is running very slow? At what points you need to go about the database in order to improve the performance?

565

---

What is the difference between database trigger and stored procedure?

557

---

How many commands are there in sql?

549

---

Can I copy :old and :new pseudo-records in/to an oracle stored procedure?

666

---

How to start the command-line sql*plus?

566

---

---

What is memory optimized table?

577

---

What is scope and visibility in PL/SQL?

598

---

How do you break a loop in pl sql?

527

---

What is date functions?

577

---

Why do we need unique key in a table?

527

---

describe mysql connection using mysql binary. : Sql dba

552

---

What does <> sql mean?

542

---

How can you fetch first 5 characters of the string?

554

---

Can delete statement be rollbacked?

527

---

Does truncate release storage space?

560

---