Orcl: Primary,Forign,Surrogate,Unique Key

Primary key and unique are Entity integrity constraints

Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist and no null values are entered

Unique key constraint is used to prevent the duplication of key values within the rows of a tableand allow null values. (In oracle, one null is not equal to another null)

Primary key can't be null but unique key can be null.

1)unique key can be null but primariy key cant be null.

2)primariy key can be refrenced to other table as FK.

3)we can have multiple unique key in a table but PK is one and only one.

4)PK in itself is unique key.

Surrogate Key: A system generated key with no business value. Usually implemented with database generated sequences.

If you have the EMPLOYEE table as the following:

CREATE TABLE EMPLOYEE ( SERIALNUMBER BIGINT NOT NULL,
FIRSTNAME CHAR(64),
LASTNAME CHAR(64),
SALARY DECIMAL(10, 2),
PRIMARY KEY (SERIALNUMBER))

 You can insert a row with the following statement:

INSERT INTO EMPLOYEE ( SERIALNUMBER, FIRSTNAME, LASTNAME,
SALARY) VALUES(NEXTVAL FOR EMPSERIAL, 'John', 'Smith', 99.99)

 Here you use the "NEXTVAL FOR EMPSERIAL" to obtain the unique value from the SEQUENCE

A foreign key is a field in a relational table that matches the primary key column of another table. The foreign key can be used to cross-reference tables