SQL Primary Key

Primary Key in Sql

A primary key uniquely identify all the records in table

In other word we can say that, the combination of unique and not null constrains is known as the primary key .

For instance  we have a database of a collage with all the students enrolled in a class and we want to find out details of a student whose  name is Ram and  whose roll number is 101. There is a possibility that we have another student with the same name and when we tell the database to fetch details based on that name, it won’t know whose details we are asking for. Therefore, we need something that uniquely identifies every record and does not confuse the database and that is where the PRIMARY KEY comes into the picture. In above example, we will have roll number/student id as a primary key which will uniquely identify each record.

Primary key on create table

My Sql:

CREATE TABLE Student (

      ID int NOT NULL,

      LastName varchar(150) NOT NULL,

      FirstName varchar(155),

      Age int,

      PRIMARY KEY (ID)

);

SQL Server / Oracle / MS Access:

CREATE TABLE Student (

       ID int NOT NULL PRIMARY KEY,

       LastName varchar(155) NOT NULL,

       FirstName varchar(155),

       Age int

     );

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Student (

    ID int NOT NULL,

    LastName varchar(255) NOT NULL,

    FirstName varchar(255),

    Age int,

    CONSTRAINT PK_Student PRIMARY KEY (ID,LastName)

);

Primary key on ALTER TABLE:

MySQL / SQL Server / Oracle / MS Access:

      ALTER TABLE Student

      ADD PRIMARY KEY (Id)  ;

how to drop a PRIMARY KEY Constraint

My Sql:

       ALTER TABLE Student

       DROP PRIMARY KEY;

SQL Server / Oracle / MS Access:

       ALTER TABLE Student

       DROP CONSTRAINT PK_Student;

Important point to Remember about Primary Key

• ·       Primary Key uniquely identify the all Record In table .
• ·       In a Table We can have Only One primary Key .
• ·       Primary Key must contain unique value .
• ·       primary key can not be null .
• ·       Primary key can not accept the duplicate value.
• ·       Primary key is the combination of not null and Unique constraint .