SqlTutorial-1 : Lesson-2 : Class-2
Microsoft SQL Server Training:
Free Online-Learning Classes (OLC) for T-SQL
Integrity Constraints: Primary Key and Unique Key Constraints

Microsoft SQL Server Training Online Learning Classes Integrity Constraints Primary Key Unique Key

1. Primary Key

  • Primary key is used to uniquely identifying each row in the table.
  • For a table there can one primary key. However, there is no primary key for weak entity.

Syntax:

[CONSTRAINT c_name]
PRIMARY KEY [CLUSTERED|NONCLUSTERED](col_name1 [{,col_name2} ...])

Examples:

(1) Defining Primary Key while table creation.

  1. PRIMAY KEY as Column Constraints.

    CREATE TABLE Employee (
          emp_no int NOT NULL CONSTRAINT emp_pk PRIMARY KEY,
          emp_name varchar(30)NOT NULL
    )
  2. PRIMARY KEY as Table Constraints.

    CREATE TABLE Employee (
      emp_no INTEGER NOT NULL,
      emp_name CHAR(20) NOT NULL,
      CONSTRAINT emp_pk PRIMARY KEY (emp_no)
    )
  3. Selecting IDENTITY column as PRIMARY KEY.

    CREATE TABLE MyTable (
        EmpId Int IDENTITY(1,1) NOT NULL CONSTRAINT PK_ID PRIMARY KEY)
  4. NONCLUSTERED PRIMARY KEY

    CREATE TABLE MyTable (
        MyID Int IDENTITY(1,1) NOT NULL,
        Description nVarChar(50) NOT NULL,
        CONSTRAINT PK_ID PRIMARY KEY NONCLUSTERED(MyID)
    )
  5. Multi-column Primary Key.

    CREATE TABLE ClassGrades(
        ClassID int, StudentID int,
        GradeLetter varchar(2), Constraint PK_CG PRIMARY KEY(ClassID,StudentID)
    )

(2) Defining Primary Key constraints after table creation.

  1. Adding PRIMAY KEY with ALTER TABLE

    CREATE TABLE employee(
       id INTEGER NOT NULL,
       name VARCHAR(20),
       salary DECIMAL(10,2),
       start_Date  DATETIME,
       region VARCHAR(10),
       city VARCHAR(20),
       managerid INTEGER
    );

    ALTER TABLE Employee 
        ADD CONSTRAINT PK_ID PRIMARY KEY CLUSTERED (ID)
  2. You cannot add another primary key to table

    CREATE TABLE MyTable (
       MyID  Int    IDENTITY(1,1) NOT NULL ,
       ID Int  NOT NULL,
       Description   nVarChar(50),
       Region  nVarChar(10) DEFAULT 'PNW',
       CONSTRAINT MyTable_PK PRIMARY KEY NONCLUSTERED (MyID,Region))
    GO

    ALTER TABLE MyTable 
        ADD CONSTRAINT PK_ID PRIMARY KEY NONCLUSTERED (MyID) // Error 
    GO

2. Unique Key

  • Unique-Key prevents duplicate values in a column. It can include Null Values.
  • A table can exists multiple Unique-keys.
  • You can modify values of Unique-key.

Syntax:

[ COSNTRAINTS constraint_Name] UNIQUE (column[ASC | DESC] [,…n])

Examples:

  1. Adding UNIQUE constraints as column constraint.

    CREATE TABLE Shippers (
       ShipperID int IDENTITY NOT NULL PRIMARY KEY,
       PhoneNo varchar(14) NOT NULL UNIQUE,
       ShipperName varchar(30) NOT NULL,
       Address varchar(30) NOT NULL,
       City varchar(25) NOT NULL,
       State char(2) NOT NULL,
       Zip varchar(10) NOT NULL, )
  2. Adding UNIQUE constraints as Table constraint.

    CREATE TABLE Employee (
       EmployeeID Int IDENTITY(1,1) NOT NULL,
       LastName nVarChar(50) NOT NULL,
       FirstName nVarChar(50) NOT NULL,
       SSN Char(9) NOT NULL,
       CONSTRAINT U_SSN UNIQUE NONCLUSTERED (SSN) 
    )
  3. Adding UNIQUE constraint to existing table.

    ALTER TABLE Employee
       ADD CONSTRAINT u_SSN UNIQUE NONCLUSTERED (SSN);
  4. Adding UNIQUE Constraints on composite column.

    CREATE TABLE customer_location (
          cust_id int NOT NULL,
          cust_location_number int NOT NULL,
          CONSTRAINT cus_loc_unq UNIQUE(cust_id, cust_location_number) )

3. Primary Key Vs Unique Key

The primary difference is Nullability and Indexing. Following are some differnces:
  • Behavior: Primary Key is used to identify a row(record) in a table whereas Unique-Key is to prevent duplicate values in a column.
  • Nullability: Primary key does not include Null values whereas Unique-key can.
  • Indexing: By default Sql-engine creates Clustered Index on primary-key if not exists and Non-Clustered Index on Unique-key.
  • Existence: A table can have at most one primary key but can have multiple Unique-key.
  • Modifiability: You can’t change or delete primary values but Unique-key values can.
UNIQUE + NOTNULL!=PRIAMRY key Unique is always refers Non-clustered index and primary is always clustered.

7 comments:

  1. Well I went through lot of online stuff for SQL, but the way it's been explained in this blog is amazing, very conceptual n helps to build a good foundation for advanced topics

    ReplyDelete
  2. Search engine optimization is a highly effective digital marketing strategy for Forex Trading Seo . It is the process of optimizing a website so that it will rank higher in search engine results.

    ReplyDelete
  3. Don't be fooled by your SEO strategy. It's important to have a strategy in place, but don't think that this is all you need to do. You also need to make sure that you're getting your site in front of the right people. Do you have a website that needs a little more exposure? We offer Backlink Services that will increase your rankings and get you in front of the right people. We offer a variety of backlink packages that will be tailored to your needs. We'll create a plan for you, so you can see how it works before you buy. What are you waiting for? Get your site in front of the right people with our backlink services today!

    ReplyDelete
  4. Are you an online trader looking for help? Do you want to reach more customers with your Forex broker and spread your message across the internet ? Here we have a solution for you and it's called - " Seo Service Provider For Forex Services by Tradingzy"

    ReplyDelete
  5. Whether you are a new trader or an experienced trader, Online Stock Broker provides all of the information you need to make an educated decision about which online broker to use. We have years of experience in this industry working alongside brokers, traders and market makers and we bring our knowledge and expertise to you with this guide.

    ReplyDelete
  6. Get the latest Fxit Stock price with our real-time quote and chart. Check the performance of Fxit Stock with historical data and read summary analysis on this stock in Our Servlogin Webpage.

    ReplyDelete
  7. Are Forex Trading Evo And Forex Brokers Confusing? Check Out Over 25 Highly Rated, Tried-and-tested Forex Brokers. We'll Help You Find The Right One For Your Needs. At Forex Trading Evo Our Mission Is To Help You Find The Right Broker So You Can Get On With What Really Matters, Trading!

    ReplyDelete