-
SQL Installing SSMS
-
SQL Syntax
-
SQL Datatype
-
SQL Database
-
SQL Table
-
SQL Select
-
SQL Distinct
-
SQL Count
-
SQL Top
-
SQL Aliases
-
SQL In
-
SQL Insert
-
SQL Update
-
SQL Null
-
SQL Like
-
SQL Wildcards
-
SQL Nested Query
-
SQL Between
-
SQL Unique
-
SQL Primary Key
-
SQL Foreign Key
-
SQL Check
-
SQL Default
-
SQL Database Diagram
-
SQL Stored Procedures
-
SQL Joins
-
SQL Inner Join
-
SQL Left Join
-
SQL Right Join
-
SQL Full Join
-
SQL Trigger
SQL Primary Key
The PRIMARY KEY constraint uniquely identifies each record in a table. Primary keys must contain UNIQUE values, and cannot contain NULL values. A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).
Example:
CREATE TABLE TableName (ColumnName1 datatype PRIMARY KEY, ColumnName2 datatype,…., ColumnNameN datatype);
The following SQL creates a PRIMARY KEY on the “ID” column when the “Persons” table is created:
CREATE TABLE Persons (
ID int PRIMARY KEY,
LastName varchar(255) ,
FirstName varchar(255),
Age int
);
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
CREATE TABLE Persons (
ID int,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
Note: In the example above there is only ONE PRIMARY KEY (PK_Person). However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).
To create a PRIMARY KEY constraint on the “ID” column when the table is already created, use the following SQL:
ALTER TABLE Persons ADD PRIMARY KEY (ID);
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
Note: If you use ALTER TABLE to add a primary key, the primary key column(s) must have been declared to not contain NULL values (when the table was first created).
To drop a PRIMARY KEY constraint, use the following SQL:
ALTER TABLE Persons DROP CONSTRAINT PK_Person;
Course Video
1. Creates a PRIMARY KEY on the “ID” column when the table is created and also add not null constrain
2.Create table with 5 column and add primary key to the first column
Hint: Add primary key to the employee id column
3.Create a table without primary key and add primary key using alter table
Hint: Primary key was added to the customer id column by alter table
4.create a new table with sales name and add the primary key with the combination of the two column
Hint: Primary key was added to the sales id and product id
5. Write a sql query to drop primary key constrain
6. Design a database schema for a hotel management system. Create a table named “Hotel_Room” with the following columns: Room_ID (primary key), Room_Name, Room_Number (unique), Room_Type, and Price_Per_Night. Ensure that each room has a unique n notnull ID and that the room numbers are unique within the hotel. Insert 10 records into the “Hotel_Room” table, ensuring that the room types are diversified (e.g., single, double, suite) and the prices per night vary accordingly. Additionally, enforce constraints to ensure that the Room_Type is not null.
7. Create a table named “Employee_Details” with the following columns: Employee_Details_ID (auto-incremented), Employee_ID, Joining_Date, Position, and Manager_ID. Insert 8 records into the “Employee_Details” table. Ensure that each record has a unique Employee_Details_ID. After creating the table, add a primary key constraint to the “Employee_Details_ID” column using the ALTER TABLE statement.
8. Craft a SQL script to define a table named “Items,” incorporating an “ID” column as the primary key, ensuring data integrity and uniqueness. Additionally, enforce the constraint of item name being “NOT NULL,”.
9. Compose a SQL query designed to alter the table structure by removing the primary key constraint from the Items table.
YouTube Reference :
1) SQL Primary Key in Hindi/Urdu
2) SQL Primary Key in English
