Database Management System

SQL KEYS

Primary Key – uniquely identifies each row in the table.

Composite primary key – A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness.

Ways to declare primary key:

-- AT time of Table creation
    CREATE TABLE table1  
    (  
    t_Id int NOT NULL,  
    LastName varchar (255) NOT NULL,  
    FirstName varchar (255),   
    City varchar (255),  
    PRIMARY KEY (S_Id)  
    )  
-- After table creation
-- single column primary key
    ALTER TABLE table1  
    ADD PRIMARY KEY (t_Id)  
-- multiple column primary key
    ALTER TABLE table1  
    ADD CONSTRAINT pk_tableID PRIMARY KEY (t_Id,LastName)  
-- another way for candidate key declration
    CREATE TABLE SAMPLE_TABLE  
    (COL1 integer,  
    COL2 varchar(30),  
    COL3 varchar(50),  
    PRIMARY KEY (COL1, COL2));  

Foreign Key

A foreign key is a column that is used to establish a link between two tables, In simple words you can say that, a foreign key in one table used to point primary key in another table.

Way to declare the Foreign Key

    -- At the time of table creation
CREATE TABLE table2  
    (  
    t2_Id int NOT NULL,  
    Order_No  int NOT NULL,  
    t1_Id int,  
    PRIMAY KEY (t2_Id),  
    FOREIGN KEY (t1_Id) REFERENCES table1 (t1_Id)  
    )  
-- after table creating using ALter
    ALTER TABLE table2  
    ADD CONSTRAINT fk_Pertable2  
    FOREIGN KEY(t1_Id)  
    REFERENCES Students (t1_Id)  

Unique Key

A unique key is a set of one or more than one columns of a table that uniquely identify a record in a database table.

There is an automatically defined unique key constraint within a primary key constraint.

Ways to define unique key

CREATE TABLE table1  
(  
t_Id int NOT NULL,  
LastName varchar (255) NOT NULL,  
FirstName varchar (255),  
City varchar (255),  
UNIQUE (S_Id)  
)  

Alternate Key

Alternate key is a secondary key(keys other than primary) it can be simple to understand by an example:

Let’s take an example of student it can contain NAME, ROLL NO., ID and CLASS.Here ROLL NO. is primary key and rest of all columns like NAME, ID and CLASS are alternate keys.

If a table has more than one candidate key, one of them will become the primary key and rest of all are called alternate keys.

In simple words, you can say that any of the candidate key which is not part of primary key is called an alternate key. So when we talk about alternate key, the column may not be primary key but still it is a unique key in the column.

Point of Clarification – is it required that the alternate key has to be the candidate key.?

Differences

Primary key vs foreign key

Primary key vs unique key