Keys in SQL

24/05/2011 12:48

/*Keys:-
------
1. Primary Key
2. Unique Key
3. Foreign Key
4. Composite Key
5. Alternate Key
-----------------------------------------------------------------
Primary Key :-

1. Primary is used to create the identical data in a table.
2. It wont allow duplicate Data or repeated data.
3. It wont allow null value.*/

CREATE TABLE TblEmpDetailsPKey
(
    EmpId        varchar(100) PRIMARY KEY,
    EmpName        nvarchar(100),
    Address        char(300),
    City        varchar(30)   
)
GO

        (OR)

CREATE TABLE TblEmpDetailsPKey
(
    EmpId        varchar(100),
    EmpName        nvarchar(100),
    Address        char(300),
    City        varchar(30),
    PRIMARY KEY(EmpId)

)
GO

INSERT    INTO dbo.TblEmpDetailsPKey   
(
        EmpId,EmpName,Address,city
)
VALUES
(       
        'EMP111','Maha','Namakkal','Salem'
)

SELECT    *
FROM    dbo.TblEmpDetailsPKey(nolock)
----------------------------------------------------------------------------------------------
/* Unique Key :-

1. Unique is used to create the identical data in a table.
2. It wont allow duplicate Data or repeated data.
3. It will allow only one null value.*/

CREATE TABLE TblEmpDetailsUNIKey
(
    EmpId        varchar(100) UNIQUE,
    EmpName        nvarchar(100),
    Address        char(300),
    City        varchar(30)   
)
GO

INSERT    INTO dbo.TblEmpDetailsUNIKey   
(
        EmpName,Address,city
)
VALUES
(       
        'Shan','Namakkal','Salem'
)


SELECT    *
FROM    dbo.TblEmpDetailsUNIKey(nolock)
---------------------------------------------------------------------------
/* Foreign Key:-

1. Create Relationship between two or more Tables.
2. Parent Table should contain Primary Key
3. Child Table should contain Foreign Key. */


CASCADE DELETE-- It will delete  the values from parent table as well as Child Table

--Rules :-
----------
--1. Both Table should contain Same Column name, same data type and same size.


CREATE TABLE TblEmpDetailsFKey
(
    EmpId        varchar(100) foreign key references TblEmpDetailsPKey NOT NULL,
    Attendance    int,
    Year        int,
    Percentage    numeric(10,2)   
)


SELECT    *
FROM    dbo.TblEmpDetailsPKey(nolock)

INSERT    INTO dbo.TblEmpDetailsFKey   
(
        Attendance,Year,Percentage
)
VALUES
(       
        348,2009,79.09
)

SELECT *
FROM    dbo.TblEmpDetailsFKey(nolock)
-----------------------------------------------------------------
/* Compsoite Key

1. More than one Key in a single Table then its called as Composite Key.*/

CREATE TABLE TblEmpDetailsComKey
(
    EmpId        varchar(100) PRIMARY KEY,
    EmpName        nvarchar(100) UNIQUE,
    Address        char(300)UNIQUE,
    City        varchar(30)

)
GO
--------------------------------------------------------------------
-- Alternate Key :-

CREATE TABLE TblEmpDetALKey
(
    EmpId        varchar(100),
    EmpName        nvarchar(100),
    Address        char(300),
    City        varchar(30),
    PRIMARY    KEY    (EmpId,EmpName,Address)   
)
GO

--Note:-
--------
/* EmpId,EmpName,Address All are Primary key
But EmpId will act as a real primary key
EmpName,Address -- AlterNateKey */

-------------------------------------------------------------------

Back