SQL Primary Key constraint is used to implement the data Integrity in tables. When you set a Primary key on any column, the Database engine will automatically create a Unique index on that column. In real-time, we use these SQL Primary key columns to identify the columns uniquely and to access the data quickly and effectively.
In general, every table has at least one column that contains unique values called the SQL Primary Key column. For example, an Employee table might have Employee ID, or Orders have an Order ID column. Before we get into the SQL Server Primary Key example, the following are the list of points you should remember:
- A table can contain Only one Primary key in SQL Server.
- You can’t insert any duplicate content into this columns.
- This column doesn’t allow NULL values. By default, NOT NULL will be assigned to the these columns, even if you forgot at the time of creation.
- If you forgot to mention the index as Clustered or Non clustered, then the Database engine will assign clustered index by default.
Create SQL Primary Key constraint
We can create a primary key constraint using both the Transact query and the Management Studio.
SQL Create Table Primary Key
Let us see how to create a Primary Key using a transact query. Here we will define it at the creation of the table.
CREATE TABLE [CustmerRecords] ( [CustomerKey] [int] NOT NULL PRIMARY KEY, [FirstName] [varchar](50) NOT NULL , [LastName] [varchar](50) NULL, [BirthDate] [date] NULL, [EmailAddress] [nvarchar](50) NULL, [Yearly Income] [money] NULL, [Profession] [nvarchar](100) NULL )
See the internal code generated by the SSMS, by right-clicking on the table -> script as – > create to new query window option
Create Primary Key on Existing table
Let us add SQL Server Primary Key on the existing table. For this, we create a new table
CREATE TABLE [CustmerRecords] ( [CustomerKey] [int] NOT NULL, [FirstName] [varchar](50) NOT NULL , [LastName] [varchar](50) NULL, [BirthDate] [date] NULL, [EmailAddress] [nvarchar](50) NULL, [Yearly Income] [money] NULL, [Profession] [nvarchar](100) NULL )
Now let me add this constraint to the above table. For this example, we are using the following Alter Table Statement to alter table content. And then, we used the ADD Constraint statement to add this constraint.
ALTER TABLE [CustmerRecords] ADD CONSTRAINT PK_CustmerRecords_CustomerKey PRIMARY KEY CLUSTERED (CustomerKey); GO
Insert rows into Primary key Constraint Column
Let me insert a few rows to check the functionality.
INSERT INTO [dbo].[CustmerRecords] VALUES (1, 'Imran', 'Khan', '10-08-1985', 'firstname.lastname@example.org', 15900, 'Skilled Professional') ,(2, 'Doe', 'Lara', '10-08-1985', 'email@example.com', 15000, 'Management') ,(3, 'Ramesh', 'Kumar', '10-08-1985', 'firstname.lastname@example.org', 65000, 'Professional')
(3 row(s) affected)
Let us see the inserted data on the SQL Primary Key column.
Insert Duplicate into Primary Constraint Column
INSERT INTO [dbo].[CustmerRecords] VALUES (1, 'Tutorial', 'Gateway', '10-08-1985', 'email@example.com', 15900, 'Skilled Professional')
It is throwing an error: Violation of constraint ‘PK_CustmerRecords_CustomerKey’. Cannot insert duplicate key in object ‘dbo.CustmerRecords’. The duplicate value is (1).
Let me insert the Null Value into the SQL Server primary key column. For this, we added new customer record with all the values except for the CustomerKey column, i.e., NULL value..
INSERT INTO [dbo].[CustmerRecords] VALUES (NULL, 'Tutorial', 'Gateway', '10-08-1995', 'firstname.lastname@example.org', 15900, 'Management')
It is also throwing an error.
Set Constraint using SSMS
To create a Primary Constraint using the Management Studio, please go to the Object Explorer. Right-click on that and select the Design option
Once you click the Design option, it will open the Custrecords in design mode. It has 7 columns of different data types, and our job is to add a SQL Primary Key constraint to the Customerkey column.
Right-click on the CustomerKey column and choose the Set Primary Key option.
Once you select the Set option, SSMS will set the constraint for you.
Modify SQL Primary key Constraint
Please select the table on which your constraint present, then go to the Indexes folder. Next, expand the Indexes folder to find the constraints available on it, and Right-click on it will open the context menu. Please select the option as per the requirement
NOTE: You can’t alter the constraint using the query. To modify the existing one, you have to delete and recreate it.
Delete SQL Primary Key using DROP Constraint
If you know the constraint name, use the DROP Constraint statement along with the ALTER TABLE Statement
ALTER TABLE [CustmerRecords] DROP CONSTRAINT PK_CustmerRecords_CustomerKey
If you don’t know the name, use the first SELECT Statement to get the Constraint name.
SELECT name FROM sys.key_constraints WHERE type = 'PK' AND OBJECT_NAME(parent_object_id) = N'CustmerRecords'; GO -- Delete or Drop ALTER TABLE [CustmerRecords] DROP CONSTRAINT PK_CustmerRecords_CustomerKey
Open the Customer records table in a designer mode to check whether we successfully removed the it on the CustomerKey column or not. As you see, there is no SQL Primary Key constraint symbol besides the CustomerKey column.