SQL - KEYS

SQL Keys:

What is a KEY?

·         A KEY  is a value used to uniquely identify a record in a table. A KEY could be a single column or combination of multiple columns

What is a primary Key? 

A primary is a single column values used to uniquely identify a database record.

·      A primary key cannot be NULL

·      A primary key value must be unique

·      The primary key values can not be changed

·         The primary key must be given a value when a new record is inserted.

SQL primary key for one column:

The following SQL command creates a PRIMARY KEY on the "S_Id" column when the "students" table is created.

CREATE TABLE students  

(  

S_Id int NOT NULL,  

LastName varchar (255) NOT NULL,  

FirstName varchar (255),  

Address varchar (255),  

City varchar (255),  

PRIMARY KEY (S_Id)  

)  ;

SQL primary key for multiple columns:      

CREATE TABLE students  

(  

S_Id int NOT NULL,  

LastName varchar (255) NOT NULL,  

FirstName varchar (255),  

Address varchar (255),  

City varchar (255),  

CONSTRAINT pk_StudentID PRIMARY KEY (S_Id, LastName)  

)  ;

SQL primary key on ALTER TABLE:

When table is already created and you want to create a PRIMARY KEY constraint on the S_Id?column you should use the following SQL:

Primary key on one column:

ALTER TABLE students  

ADD PRIMARY KEY (S_Id);  

Primary key on multiple column:

ALTER TABLE students  

ADD CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LastName) ; 

                

How to DROP a PRIMARY KEY constraint?

If you want to DROP (remove) a primary key constraint, you should use following syntax:

ALTER TABLE students  

DROP PRIMARY KEY ;

What is Foreign Key?

Foreign Key references primary key of another Table!It helps connect your Tables

·      A foreign key can have a different name from its primary key

·      It ensures rows in one table have corresponding rows in another

·      Unlike Primary key they do not have to be unique. Most often they aren't

·         Foreign keys can be null even though primary keys can not

SQL FOREIGN KEY constraint ON CREATE TABLE:

(Defining a foreign key constraint on single column)

To create a foreign key on the "S_Id" column when the "Orders" table is created:

CREATE TABLE orders  

(  

O_Id int NOT NULL,  

Order_No  int NOT NULL,  

S_Id int,  

PRIMAY KEY (O_Id),  

FOREIGN KEY (S_Id) REFERENCES Persons (S_Id)  

)  ;

SQL FOREIGN KEY constraint for ALTER TABLE:

If the Order table is already created and you want to create a FOREIGN KEY constraint on the ?S_Id? column, you should write the following syntax:

Defining a foreign key constraint on single column:

ALTER TABLE Orders  

ADD CONSTRAINT fk_PerOrders  

FOREIGN KEY(S_Id)  

REFERENCES Students (S_Id)  

DROP SYNTAX for FOREIGN KEY COSTRAINT:

ALTER TABLE Orders  

ROP FOREIGN KEY fk_PerOrders ; 

Difference between primary key and foreign key in SQL:

• Primary key cannot be null on the other hand foreign key can be null.
• Primary key is always unique while foreign key can be duplicated.
• Primary key uniquely identify a record in a table while foreign key is a field in a table that is primary key in another table.
• There is only one primary key in the table on the other hand we can have more than one foreign key in the table.

What is Composite Key?

·         A composite key is a primary key composed of multiple columns used to identify a record uniquely.

·         In our database , we have two people with the same name Roberl but they live at different places.

·         Hence we require both Full Name and Address to uniquely identify a record. This is a composite key.

SQL Syntax to specify composite key:

CREATE TABLE TABLE_NAME  

(COLUMN_1, DATA_TYPE_1,  

COLUMN_2, DATA_TYPE_2,  

???  

PRIMARY KEY (COLUMN_1, COLUMN_2, ...));  

What is Unique Key in SQL?

• A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table.
• You can say that it is little like primary key but it can accept only one null value and it cannot have duplicate values.
• The unique key and primary key both provide a guarantee for uniqueness for a column or a set of columns.
• There is an automatically defined unique key constraint within a primary key constraint.
• There may be many unique key constraints for one table, but only one PRIMARY KEY constraint for one table.

SQL UNIQUE KEY constraint on CREATE TABLE:

If you want to create a UNIQUE constraint on the S_Id column when the student table is created, 

CREATE TABLE students   

(  

S_Id int NOT NULL,  

LastName varchar (255) NOT NULL,  

FirstName varchar (255),  

City varchar (255),  

UNIQUE (S_Id)  

)  

SQL UNIQUE KEY constraint on ALTER TABLE:

If you want to create a unique constraint on ?S_Id? column when the table is already created, you should use the following SQL syntax:

ALTER TABLE students  

ADD UNIQUE (S_Id)  

What is Alternate Key in SQL?

• Alternate key is a secondary key 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.