Oracle Interview Question

What are the different constraints available in oracle?

There are two types of database constraint available in oracle.
One is I/O constraint and another one is business rule constraint.
I/O - pk, fk, unique, not null
Business rule - check

What is primary key?

It is a database constraint .If we declare a column as primary key it will take only UNIQUE and NOT NULL values. So we can identify each row uniquely.

Example: Suppose there is a table called employee. It has columns like employee id, name and salary. If we declare employee id as primary key in employee table then during data insertion it will check for the unique and not null values for employee id column.

What is foreign key?

It is a referential integrity constraint. It is used to maintain the relationship between two tables. It establishes a relationship between primary key or a unique key in the same Table or different Table.

Is it possible to have Primary and foreign key in the same table?

Ans: Yes! Suppose I have employee table and employee id is Primary key. Now in the same table manager ID is declared as foreign key in the employee table itself.

 If we declare a column as foreign key in a table column it will take only primary key values.

What are the different entity relationships can exists?

1:1 (Foreign key will be in any table of the two tables)
M:1 (Foreign key for the relationship will be in the base table)       
1: M (Foreign key for the relationship will be in foreign key table)
M:M (Inter table is required to maintain the relationship)

What are the Table level constraints?

If data constraints are defined after defining all the table columns when creating or altering a table structure, its called table level constraint. These constraints are stored as part of the global table definition by the oracle engine in its system table.

CREATE TABLE department (
dept_id              number(5),
name                 varchar2(30),
dept_name        varchar2(30),
CONSTRAINT dept_id_pk PRIMARY KEY (dept_id ));

What are column level constraints?

Ans: If data constraints are defined along with the column definition when creating or altering a table structure, they are called column level constraints. They are local to a specific column.

CREATE TABLE department (
dept_id              number(5) PRIMARY KEY,
name                 varchar2(30),
dept_name        varchar2(30));

 

 

Next>>