A primary key constraint is a key that has one or more columns to help identify each tuple or record in a table individually.
Qualities
Only distinct values should be assigned to the column designated as the primary key; duplicate values are forbidden. The primary key column contains no NULL values. As a result, there is a necessary value in the column holding the primary key. There is only one primary key per table, even if a primary key may contain more than one column.
Adding a new row using an existing primary key is not feasible.
categorized as,
- A single-column, basic primary key.
- Multiple columns make up the composite primary key.
As stated in the Make a Table or Modify a table statement.
A PRIMARY KEY constraint can be used to create the primary key in a table.
It can be created at a couple of levels.
SQL Primary Key at Column level
The primary key should be defined at the column level if it only has one column. The primary key "primary_field_name" is created in the table by the following code.
Syntax
Field_name data_type PRIMARY KEY
Example
CREATE TABLE Employee
(
Id INT NOT NULL PRIMARY KEY,
[Name] VARCHAR(50) NOT NULL,
City VARCHAR(20) NOT NULL
)
Let’s verify the primary key with an example.
INSERT INTO Employee
VALUES (1, 'Peter', 'Scott');
Let’s try the execution of the same query again in SQL.
It will throw an exception.
Let’s try with the NULL inserted as the primary key value.
INSERT INTO Employee
VALUES (NULL , 'Peter', 'Scott');
It will throw an error due to null not being allowed in the primary key.
SQL Primary key at Table level
Whenever the primary key contains more than one column, it has to be specified at the table level.
Syntax
CREATE TABLE table_name
(
field1 data_type,
field2 data_type,
...
PRIMARY KEY (field1)
)
Example
CREATE TABLE Employee
(
Id INT NOT NULL,
[Name] VARCHAR(50) NOT NULL,
City VARCHAR(20) NOT NULL,
PRIMARY KEY (Id)
)
Primary Key with Multiple Columns
If you want to add a primary key to more than one column, then that is not possible at the column level; that is possible at the table level.
CREATE TABLE Employee
(
Id INT NOT NULL,
[Name] VARCHAR(50) NOT NULL,
City VARCHAR(20) NOT NULL,
PRIMARY KEY (Id, [Name])
)
Primary Key with Alter Table
The primary key is typically defined at the time the table is created, but occasionally it might not be created in the table that already exists. However, we can use the Alter Statement to add the primary key.
For a Single Column
Syntax
ALTER TABLE Table_name
ADD PRIMARY KEY (column1);
Example
ALTER TABLE Employee
ADD PRIMARY KEY (Id);
For Multiple Columns
Syntax
ALTER TABLE Table_name
ADD CONSTRAINT primary_key_name PRIMARY KEY (column1, column2);
Example
ALTER TABLE Employee
ADD CONSTRAINT Employee_PK PRIMARY KEY (Id, [Name]);
Remove Primary Key
We can remove the primary key constraint from a table using the DROP statement.
Syntax
ALTER TABLE table_name
DROP CONSTRAINT primary_key_name;
Example
ALTER TABLE Employee
DROP CONSTRAINT Employee_PK;
We learned the new technique and evolved together.
Happy coding!
HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.
