Add Foreign Key in Oracle

In this section we will learn on defining a Foreign Key Constraint. In simple words lets try to define a relationship between two tables.

Lets create a Employee table with below structure.


CREATE TABLE Employee(
EMP_ID int NOT NULL,
ENAME varchar(50) NOT NULL,
DOB date NOT NULL,
SALARY decimal(18, 0) NOT NULL,
DEPTNO int NOT NULL
)

Now let’s define Primary Key on EMP_ID column, though this is not mandatory but it is a good practice. Check out the link on Adding a Primary Key on the table.

Let’s look at the syntax:


ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMPLOYEE_01 FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)


It is very simple isn't it? Similar to Primary Key syntax with a little change.

List all the Primary Keys in Oracle Database

The below script will give you list of Primary Keys in a database and columns which are part of the primary key definition.

If you don't know how to add a primary key on a table then you need to check out the "Adding Primary Key on a Table" first.


SELECT CONSTRAINT_NAME,TABLE_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'DEPT' AND CONSTRAINT_TYPE = 'P'

There could be several ways to get the same kind of information but from my point of view this is most simple way to do it.

If you do not want to limit your result set to a table then remove the TABLE_NAME condition.

Please let me know if you have a query which is much simple than this.....

Add Primary Key in Oracle

One of the cirtical factor in database/table designing is to prevent duplicate records. There are several ways to do this. You can place Unique Index or Unique Constraint or add a primary key. Well there is only slight difference between these things.

I will go one by one to describe about these but for now lets concentrate on adding a primary key to a table.

There are two ways to add a primary key constraint:
1) While Creating the Table using CREATE TABLE command.
2) Adding Primary key after table creation using ALTER TABLE statement.

The simple thing is to create the table first and define the Primary key later. Again each one of us will have different opinion but to me this is simple step. Though you will end up in having multiple SQL statements but you will have more control on what you are doing.

The pre-requisite to define a Primary Key on a column is, that column should be NOT Nullable.Check out Adding NOT NULL constraint article to meet the pre-requisite of Primary key.

To verify whether the column is NOT NULL or not, run the below query:

SELECT column_name,NULLABLE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'DEPT'

If NULLABLE is "N" means column has Not Null Constraint.

Lets say I want to add Primary Key constraint on DEPTNO Column.

ALTER TABLE:

ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO);

We are trying to add a Primary Key Constraint on DEPT table and the column is DEPTNO.The Name of the Constraint is PK_DEPT. Its very important to give a name to the constraint.As the general standards Primary key constraint name should always start with PK_ and followed by the table Name Or you can have _PK.

CREATE TABLE:

CREATE TABLE DEPT(DEPTNO NUMBER PRIMARY KEY, DNAME VARCHAR2(30))

Add NOT NULL Constraint in Oracle

Today I will explain about the different ways to add a NOT NULL constraint on a column.

Advantages of NOT NULL constraint:
1) You can prevent users to enter null values into this column.
2) The pre-requisite to enforce Primary Key on a Column.

Now Lets get into the details:
There are two ways which i can think of
1) While creating the table using CREATE TABLE statement.
2) Modifiying the table definition using ALTER TABLE statement.

CREATE TABLE:
CREATE TABLE DEPT(DEPTNO NUMBER NOT NULL, DNAME VARCHAR2(30))

When you are creating a table as part the Column Definition you can specify the Constraint. This is very easy. But most of the cases we get into a position where we have a table in PROD or some where and want to define a Not Null constraint on the column.

ALTER TABLE:

Created a table called Dept to store Department information.

CREATE TABLE DEPT(DEPTNO NUMBER, DNAME VARCHAR2(30));

ALTER TABLE DEPT MODIFY DEPTNO NOT NULL;

That's it. Now if you use any of following queries you can see the column is now not nullable.

SELECT column_name,NULLABLE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'DEPT'

or

DESC DEPT

Bitmap Index

Bitmap indexes are the most suitable for columns having very few unique values (very low cardinality). Ideally to choose the bitmap indexes as the right choice for a column the cardinality should be less than or equal to 0.05%.

Table should have no insert, update , delete or very few dml statements can act as good choice for bitmap indexes.

Bitmap indexes are highly compressed and usually take very less space when compared to B-tree indexes and are extensively used in data warehouses.

Advantage of Bitmap Indexes
Due to the nature of highly compressed structure of an bitmap index, Data can be accessed much faster than traditional B-tree indexes.

Bitmap index represent a good trade-off between disk usage and Cpu cost. A bitmap index requires more cpu cycles to decompress but requires less amount of work from disk I/O.

Disadvantages of Bitmap Indexes
Bitmap Indexes are traditionally meant to be developed for data warehouses. A modification to a bitmap index requires more work than a modification to b-tree indexes.

Deadlock’s On bitmap indexes
Bitmap indexes are not suitable for large concurrent single row Dml operations.

If there are 3 or more sessions simultaneously working to insert into a table which is bitmap indexed a dead lock situation can occur.

use_hash hint

The use_hash hint is the most useful for joining a medium size table with a large table. The throughput is high for use_hash hint when compared to Use_NL hint. use_hash hint is mostly used in datawarehousing environments where the overall query execution should be faster returning all rows rather than first row response time.

The use_has hint requests a hash join against the specified tables. Essentially a hash join is a technique wherey Oracle loads the rows from the driving table (the smallest table,after the where clause) into user global area( RAM) defined by the hash_area_size in the init.ora file.

Oracle then uses a hashing technique to locate the rows in the Larger second table.

The following query is an example of using Use_hash hint

Select /*+ use_hash( e,b) */ e.ename, b.bon, hiredate, from emp e, bonus b where e.ename=b.ename;

Source:http://www.expertsharing.com/2008/01/25/use_hash-hint-in-oracle/

ROWID in Oracle

For each row in the database, the ROWID pseudocolumn returns the address of the row. Oracle Database rowid values contain information necessary to locate a row:

The data object number of the object

The data block in the datafile in which the row resides

The position of the row in the data block (first row is 0)

The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.

Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.

Rowid values have several important uses:

They are the fastest way to access a single row.

They can show you how the rows in a table are stored.

They are unique identifiers for rows in a table.

SELECT ROWID, last_name
FROM employees
WHERE department_id = 20;