Oracle constraints FAQs

Oracle constraints - overview. What are the types of constraints available in oracle

Oracle constraints are used to maintain consistent of data and ensure the data is properly maintained. A constraint is more or less a restriction we try to apply on a table.

Types of constraints:

- Check constraints
- NOT NULL constraint
- PRIMARY KEY constraint
- REFERENCES constraint
- UNIQUE constraint

Explain Oracle "Check" constraint.

Oracle check constraint is used to ensure that before inserting the data in the database, it is validated and checked for the condition.

Example:Below, the constraint is that the id has to be between 0 and 1000.
create table employee ( id number check (id between 0 and 1000), Name varchar(200) );

Explain Not Null constraint.

Oracle NOT NULL is used on a column to ensure that the value for that column can never be NULL.

Example: Below, the constraint is that the id should never be NULL. If it is, oracle throws an error.
create table employee ( id number NOT NULL, Name varchar(200) );

Explain primary key constraint.

Primary key constraint ensures that the column(s) always has a unique value to identify the record.

Example:Below, the primary key is created for column id with name prim_id.
create table employee ( id number NOT NULL, Name varchar(200) Constraint prim_id primary key(id) );

Explain oracle foreign key constraint.

A foreign key is a reference to another table. It is used to establish relationships between tables. For example, relationship between employee and professor table. One employee can have multiple professors. The Primary key of employee becomes foreign key of professor.

Example:
create table employee ( id number NOT NULL, professor_id NOT NULL, Name varchar(200) Constraint prim_id Foreign key(id) references professor(professor_id) );

Explain unique Constraint.

A unique constraint on a column uniquely identifies the record by a combination of one or more fields. Few unique constraint fields can have a NULL value as long as the combination of values is unique.

Example:
create table employee ( id number NOT NULL, dob DATE, professor_id NOT NULL, Name varchar(200) Constraint id_unique UNIQUE(id,dob) );

Explain with an example how to alter oracle table constraint.

Alter table command can be used to alter oracle table constraint. Alter can be adding a constraint, enable or disable constraints etc

Below is an example to drop constraint using alter command:
ALTER TABLE employee
drop constraint id_unique;
Restricting and sorting data in oracle
Restricting and sorting data in oracle - Explain how to limit the rows that are retrieved by a query, Explain how to sort the rows that are retrieved by a query, What are the comparison operators in oracle. List them with description...
Working with multiple tables joins
Multiple tables joins - What are joins? Explain its characteristic features, Cross joins, Inner joins, Outer joins, Equi- and non-equi-joins, Self joins...
Working with multiple tables
Working with multiple tables - Define subquery. Explain with an example, What is correlated query? Explain its uses, What are Set Operators? Explain union and union all with an example for each...
Post your comment

You may like