Primary Key or Unique Index

Difference between Primary Key and Unique Index in SQL Server

From SQL UNIQUE Constraint

The UNIQUE constraint uniquely identifies each record in a database
table.

The UNIQUE and PRIMARY KEY constraints both provide a
guarantee for uniqueness for a column or set of columns.

A PRIMARY
KEY constraint automatically has a UNIQUE constraint defined on it.

Note that you can have many UNIQUE constraints per table, but only one
PRIMARY KEY constraint per table.

Also, from Create Unique Indexes

You cannot create a unique index on a single column if that column
contains NULL in more than one row. Similarly, you cannot create a
unique index on multiple columns if the combination of columns
contains NULL in more than one row. These are treated as duplicate
values for indexing purposes.

Whereas from Create Primary Keys

All columns defined within a PRIMARY KEY constraint must be defined as
NOT NULL. If nullability is not specified, all columns participating
in a PRIMARY KEY constraint have their nullability set to NOT NULL.

When should I use primary key or index?

Basically, a primary key is (at the implementation level) a special kind of index. Specifically:

A table can have only one primary key, and with very few exceptions, every table should have one.
A primary key is implicitly UNIQUE - you cannot have more than one row with the same primary key, since its purpose is to uniquely identify rows.
A primary key can never be NULL, so the row(s) it consists of must be NOT NULL

A table can have multiple indexes, and indexes are not necessarily UNIQUE. Indexes exist for two reasons:

To enforce a uniquness constraint (these can be created implicitly when you declare a column UNIQUE)
To improve performance. Comparisons for equality or "greater/smaller than" in WHERE clauses, as well as JOINs, are much faster on columns that have an index. But note that each index decreases update/insert/delete performance, so you should only have them where they're actually needed.

MySQL: what's the difference between INDEX, UNIQUE, FOREIGN KEY, and PRIMARY KEY?

Expanding on Shamil's answers:

INDEX is similar to the index at the back of a book. It provides a simplified look-up for the data in that column so that searches on it are faster. Fun details: MyISAM uses a hashtable to store indexes, which keys the data, but is still linearly proportional in depth to the table size. InnoDB uses a B-tree structure for its indexes. A B-tree is similar to a nested set - it breaks down the data into logical child groups, meaning search depth is significantly smaller. As such, lookups by ranges are faster in a InnoDB, whereas lookups of a single key are faster in MyISAM (try to remember the Big O of hashtables and binary trees).

UNIQUE INDEX is an index in which each row in the database must have a unique value for that column or group of columns. This is useful for preventing duplication, e.g. for an email column in a users table where you want only one account per email address. Important note that in MySQL, an INSERT... ON DUPLICATE KEY UPDATE statement will execute the update if it finds a duplicate unique index match, even if it's not your primary key. This is a pitfall to be aware of when using INSERT... UPDATE statements on tables with uniques. You may wind up unintentionally overwriting records! Another note about Uniques in MySQL - per the ANSI-92 standard, NULL values are not to be considered unique, which means you can have multiple NULL values in a nullable unique-indexed column. Although it's a standard, some other RDBMSes differ on implementation of this.

PRIMARY KEY is a UNIQUE INDEX that is the identifier for any given row in the table. As such, it must not be null, and is saved as a clustered index. Clustered means that the data is written to your filesystem in ascending order on the PK. This makes searches on primary key significantly faster than any other index type (as in MySQL, only the PK may be your clustered index). Note that clustering also causes concerns with INSERT statements if your data is not AUTO_INCREMENTed, as MySQL will have to shift data around on the filesystem if you insert a new row with a PK with a lower ordinal value. This could hamper your DB performance. So unless you're certain you know what you're doing, always use an auto-incremented value for your PK in MySQL.

FOREIGN KEY is a reference to a column in another table. It enforces Referential Integrity, which means that you cannot create an entry in a column which has a foreign key to another table if the entered value does not exist in the referenced table. In MySQL, a FOREIGN KEY does not improve search performance. It also requires that both tables in the key definition use the InnoDB engine, and have the same data type, character set, and collation.

difference between primary key and unique key

Primary Key:

There can only be one primary key constraint in a table
In some DBMS it cannot be NULL - e.g. MySQL adds NOT NULL
Primary Key is a unique key identifier of the record

Unique Key:

Can be more than one unique key in one table
Unique key can have NULL values
It can be a candidate key
Unique key can be NULL ; multiple rows can have NULL values and therefore may not be considered "unique"

which index gets automatically created when we create primary key and unique key in Oracle

Oracle will create a unique index in both cases. A primary key can not contain null values. A unique key can.

If you want to try and see what happens, information about indexes is available in the user_indexes view.