SQL PRIMARY KEY Constraint

SQL PRIMARY KEY Constraint


The SQL PRIMARY KEY constraint is used to add unique or distinct value in a field and uniquely identifies each row in a database table.

All Primary key columns must contain PRIMARY KEY values and it will not support NULL value.

A database table can have only ONE SQL PRIMARY KEY constraint column, which may consist of single or multiple columns. When multiple columns are used as a primary key, they are called a composite key.

Difference Between Unique Key and Primary Key

There are many differences between them, which is mentioned below:

Unique Key Primary Key
Unique Value Yes Yes
Null Value Support only once in a column Does not support null value
Column Limit Support multiple column can have unique key Support for only one column in a table


Sql primary key constraint using primary key vs unique key, alter table add primary key, create table primary key, update and drop primary key, composite primary key, unique key, create table sql server primary key, add constraint primary key Auto Generate, Auto Increment, Primary Key Name Convention, difference between primary key, foreign key and unique key,.

SQL PRIMARY KEY Constraint on CREATE TABLE

The following SQL statement creates a SQL PRIMARY KEY constraint on the "BookID" field when the "Books" table is created:

For MySql


CREATE TABLE Books
(
BookID INT NOT NULL,
BookName VARCHAR(255),
Description VARCHAR(255),
PRIMARY KEY(BookID)
);

For Microsoft SQL Server / Oracle / Microsoft Access


CREATE TABLE Books
(
BookID INT NOT NULL PRIMARY KEY,
BookName VARCHAR(255),
Description VARCHAR(255),
BookPrice DECIMAL (8, 2)
);

In the above example, the following SQL statement creates a new table called Books and adds four fields.

Here, BookID field has a SQL PRIMARY KEY constraint to validate newly entered records for unique value.

Let's assume, if the "Books" table already contains the following records:

BookId BookName Description
101 Sql Complete Reference It descripes how to write and execute SQL statement into database.
102 Sql Commands It exaplins a list of SQL command.
103 Pl Sql Quick Programming How to write and execute SQL programming using pl sql.
104 Sql Query Injection How to hack SQL queries in database.

Now executing the following below SQL statement:


INSERT INTO Books (BookID, BookName, Description)
VALUES (103, 'Sql Quick Programming', 'It descripe complete sql programming in short time');

The above SQL statement will produce in an error, because '103' already exists in the BookID field, thus trying to insert another record with that value violates the SQL PRIMARY KEY constraint.

To allow naming of a SQL PRIMARY KEY constraint, and for defining a SQL PRIMARY KEY constraint on multiple fields, use the following SQL syntax:

For Microsoft SQL Server / Oracle / Microsoft Access / MySql


CREATE TABLE Books
(
BookID INT NOT NULL,
BookName VARCHAR(255) NOT NULL,
Description VARCHAR(255),
CONSTRAINT chk_BookID PRIMARY KEY (BookID, BookName)
);

Note: In the example above there is only ONE SQL PRIMARY KEY (chk_BookID). However, the VALUE of the primary key is made up of TWO FIELDS (BookID + BookName).


SQL PRIMARY KEY Constraint on ALTER TABLE

To create a SQL PRIMARY KEY constraint on the "BookID" field when the table is already created, use the following SQL statement:

For Microsoft SQL Server / Oracle / Microsoft Access / MySql


ALTER TABLE Books
ADD PRIMARY KEY (BookID);

To create a SQL PRIMARY KEY constraint on multiple fileds when the table is already created, use the following SQL statement:

For Microsoft SQL Server / Oracle / Microsoft Access


ALTER TABLE Books
ADD CONSTRAINT chk_BookID PRIMARY KEY (BookID, BookName);

Note: If you want to add a primary key using SQL ALTER TABLE statement, the primary key field(s) must already have been declared to not contain NULL values (when the table was first created).


To DROP a SQL PRIMARY KEY Constraint

To drop or remove a SQL PRIMARY KEY constraint, use the following SQL statement:

For MySQL


ALTER TABLE Books
DROP PRIMARY KEY;

For Microsoft SQL SERVER / Microsoft ACCESS / ORACLE


ALTER TABLE Books
DROP CONSTRAINT chk_BookID;


Sql server primary key constraint used to primary key multiple columns, multiple primary keys, violation of primary key constraint, set primary key, multiple field primary key not null, two columns, more than one primary key, identity sql server primary key two columns, Reset, Primary Key Start 1, Primary Key vs Foreign Key, Primary Foreign Same Time, create table composite primary key.

SQL Primary Key Alert Table

You can generate a primary key using the ALTER TABLE statement if your table already exists and you want to add one later.

Syntax:

The syntax to create a primary key using the ALTER TABLE statement in SQL is:

ALTER TABLE table_name
 ADD CONSTRAINT constraint_name
 PRIMARY KEY (column1, column2, ... column_n);
  • table_name - The name of the table you want to change. This is the table to which a primary key should be added.
  • constraint_name - The name of the primary key.
  • column1, column2, ... column_n - The columns that make up the primary key.

Example 1: Let's look at an example of utilising the ALTER TABLE statement in SQL to generate a primary key. Let's pretend we've already set up a suppliers table in our database. The ALTER TABLE statement below could be used to add a primary to the suppliers table:

ALTER TABLE suppliers
  ADD CONSTRAINT suppliers_pk 
  PRIMARY KEY (supplier_id);

We've added a primary key called suppliers_pk on the current suppliers table in this instance. The supplier_id column is included.

We could also create a primary key with more than one field as in the example below:

ALTER TABLE suppliers
  ADD CONSTRAINT suppliers_pk
  PRIMARY KEY (supplier_id, supplier_name);

This example would created a primary key called suppliers_pk that is made up of a combination of the supplier_id and supplier_name columns.

Example 2: When the "customers" table is already formed and you only want to change it, you can construct a primary key on the "customerID" column:

ALTER TABLE Customers
ADD PRIMARY KEY (CustomerID);

Use the following SQL syntax to give the Primary Key constraint a name and define it on several columns:

ALTER TABLE Customers
ADD CONSTRAINT PK_Customer PRIMARY KEY (CustomerID,CustomerName);

SQL Primary Key Auto Generate

Auto increment in primary key

When providing unique primary keys to most database tables, all relational databases are the best choice.

When a new record is entered into a table, auto-increment generates a unique number for that record.

This is frequently the primary key field that we want to be produced automatically whenever a new record is added.

The benefits of having numeric, auto incremented primary keys are numerous, but the most significant are improved query speed and data independence when searching through thousands of entries that may contain constantly changing data elsewhere in the table. Applications can benefit from these speedier and more reliable inquiries provided they use a consistent and unique numeric identity.

Syntax:

The "Personid" column in the "Persons" table is defined as an auto-increment primary key field in the SQL statement below:

CREATE TABLE Persons (
 Personid int NOT NULL AUTO_INCREMENT,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Age int,
 PRIMARY KEY (Personid)
);

The default value for AUTO INCREMENT is 1, and it will increase by one for each new record.

Example 1: Once you've connected to SQL Server, you'll typically begin by CREATING a new table with the field you want to use as your incremented primary key. We'll use the tried-and-true id field for our instance:

CREATE TABLE books (
  id              INT           NOT NULL,
  title           VARCHAR(100)  NOT NULL,
  primary_author  VARCHAR(100),
);

The difficulty is that we have no control over our id field. When a new record is created, we must not only manually enter an id value, but we must also run a query ahead of time to ensure that the id value doesn't yet exist (a near-impossibility when dealing with many simultaneous connections).

Example 2: Using Identity and Primary Key Constraints

The solution turns out to be utilising two SQL Server constraint settings.

The first is PRIMARY KEY, which makes the given column operate as a completely unique index for the table, allowing for faster searching and queries.

While SQL Server permits just one PRIMARY KEY constraint per table, it can be defined for many columns. Certain columns in a multi-column situation may include duplicate, non-unique values, but the PRIMARY KEY constraint assures that every restricted value combination is unique in relation to every other combination.

The IDENTITY constraint tells SQL Server to automatically increment the numeric value within the selected column whenever a new record is INSERTED, which is the second piece of the puzzle. While the IDENTITY constraint accepts two parameters, the numeric seed and the increment, these values are often not supplied with the IDENTITY constraint and are instead left as defaults (both default to 1).

We can rewrite our previous CREATE TABLE statement by adding our two new constraints.

CREATE TABLE books (
  id              INT           NOT NULL    IDENTITY    PRIMARY KEY,
  title           VARCHAR(100)  NOT NULL,
  primary_author  VARCHAR(100),
);

That’s all there is to it. Now the id column of our books table will be automatically incremented upon every INSERT and the id field is guaranteed to be a unique value as well.

Example 3: The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:

CREATE TABLE Persons (
    Personid int IDENTITY(1,1) PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

The IDENTITY keyword in MS SQL Server is used to perform an auto-increment functionality.

The beginning value for IDENTITY in the example above is 1, and it will increase by 1 with each additional entry.

Tip: Modify it to IDENTITY to specify that the "Personid" column should start at 10 and increase by 5. (10,5).

To insert a new record into the "Persons" table, we will NOT have to specify a value for the "Personid" column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen');

The SQL line above modifies the "Persons" table with a new record. A unique value would be assigned to the "Personid" field. "Lars" would be entered in the "FirstName" field, and "Monsen" would be entered in the "LastName" column.


SQL Primary Key Auto Increment

To ensure that data retains its unique identity, we use the auto-increment attribute on the Primary Key attribute.

The benefits of having numeric, auto incremented primary keys are numerous, but the most significant are improved query speed and data independence when searching through thousands of entries that may contain constantly changed data elsewhere in the table. Applications can benefit from these speedier and more reliable inquiries if they have a consistent and unique numeric identity.

Syntax:

1. To create an Auto Increment Column.

CREATE TABLE tableName (
col1 NOT NULL AUTO_INCREMENT,
Col2, col3,......
PRIMARY KEY(col1)
);

2. To alter the starting point of the Auto Increment Column.

ALTER TABLE tableName AUTO_INCREMENT = n;

3. To alter the amount of increment of each column.

CREATE TABLE tableName (
col1 NOT NULL AUTO_INCREMENT(n,m),
Col2, col3,......
PRIMARY KEY(col1)
);

Where n is the starting point and m is the gap between each created record.

Features of SQL Auto Increment:

  1. Enables the creation of a Primary Key in data that lacks a unique identification property.
  2. We can specify the initial value explicitly and change it at any moment.
  3. Assists us in creating unique records identification.
  4. Allows us to handle the interval between each record with freedom.
  5. We can leave the auto-increment attribute empty because it will take its values automatically.

Example 1: Basic Table Creation

Once you've connected to SQL Server, you'll usually begin by CREATING a new table with the field you want to use as your incremented primary key.

For our example, we’ll stick with the tried and true id field:

CREATE TABLE books (
  id              INT           NOT NULL,
  title           VARCHAR(100)  NOT NULL,
  primary_author  VARCHAR(100),
);

The issue is that we don't have any control over our id field. When creating a new record, we must not only manually enter an id value, but also run a query to ensure that the id value does not already exist (a near-impossibility when dealing with many simultaneous connections).

Example 2: Steps to create an Auto Increment Attribute

Step 1. Let us create a new database called DataFlair_emp with the following columns.

ID int Auto Increment NOT NULL
Name varchar(50)
Location varchar(50)
USE DataFlair;
CREATE TABLE DataFlair_emp(
ID int NOT NULL auto_increment,
Name varchar(50),
Location varchar(50),
PRIMARY KEY(ID));

We can see that our database with ID as the Auto Increment attribute is created.

Step 2. Let us now insert data in our database.

USE DataFlair;
INSERT INTO DataFlair_emp(Name, Location) VALUES
("Amit","Indore"),
("RAJ","Pune"),
("Rita","Noida"),
("Shiv","Indore"),
("Ram","Noida");

SELECT * FROM DataFlair_emp; 

Despite the fact that no value or ID field was supplied, the values were automatically incremented and stored into our database.

Step 3. Let us now alter the starting value of Autoincrement and then again insert some new records in our database.

USE DataFlair;
ALTER TABLE DataFlair_emp AUTO_INCREMENT = 100 ;
INSERT INTO DataFlair_emp(Name,Location) VALUES
("Riya","Indore"),
("Shriya","Pune");

SELECT * FROM DataFlair_emp; 

We can see that the Auto Increment value is now set to 100, and that the values are being increased by 1 and placed in our database as Primary Key by inserting numerous records.

Example 3: The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

CREATE TABLE animals (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  name CHAR(30) NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('lax'),('whale'),('ostrich');

SELECT * FROM animals;

Output:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

Because the AUTO_INCREMENT column had no value, MySQL assigned sequence numbers automatically. Unless the NO_AUTO_VALUE_ON_ZERO SQL option is selected, you can also directly assign 0 to the column to produce sequence numbers. For example:

INSERT INTO animals (id,name) VALUES(0,'groundhog');

It is also feasible to assign NULL to a column that is marked NOT NULL to create sequence numbers. For example:

INSERT INTO animals (id,name) VALUES(NULL,'squirrel');

When you insert any other value into an AUTO INCREMENT column, the column is set to that value, and the sequence is reset so that the next automatically generated value follows the largest column value in a sequential order. For example:

INSERT INTO animals (id,name) VALUES(100,'rabbit');
INSERT INTO animals (id,name) VALUES(NULL,'mouse');

SELECT * FROM animals;

Output:

+-----+-----------+
| id  | name      |
+-----+-----------+
|   1 | dog       |
|   2 | cat       |
|   3 | penguin   |
|   4 | lax       |
|   5 | whale     |
|   6 | ostrich   |
|   7 | groundhog |
|   8 | squirrel  |
| 100 | rabbit    |
| 101 | mouse     |
+-----+-----------+

The AUTO_INCREMENT sequence is reset when an existing AUTO_INCREMENT column value is changed.

The LAST_INSERT_ID() SQL method or the mysql_insert{id() C API function can be used to get the most recent AUTO_INCREMENT value created automatically. These functions are connection-specific, therefore their return values are unaffected by inserts from another connection.

For the AUTO INCREMENT column, use the smallest integer data type that can carry the maximum sequence value you need. The following attempt to produce a sequence number fails when the column surpasses the top limit of the data type. If possible, use the UNSIGNED characteristic to allow for a wider range. If you use TINYINT, for example, the maximum sequence number you can have is 127. The maximum value for TINYINT UNSIGNED is 255. For the ranges of all the integer types, see Section 11.1.2, "Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT."

Example 4:

LAST_INSERT_ID() and mysql_insert_id() return the AUTO INCREMENT key from the first of the inserted rows in a multiple-row insert. Multiple-row inserts can now be appropriately replicated on additional servers in a replication setup.

Set an AUTO INCREMENT value other than 1 with CREATE TABLE or ALTER TABLE, as in this example:

ALTER TABLE tbl AUTO_INCREMENT = 100;

you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.

<CREATE TABLE animals (
    grp ENUM('fish','mammal','bird') NOT NULL,
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    name CHAR(30) NOT NULL,
    PRIMARY KEY (grp,id)
) ENGINE=MyISAM;

INSERT INTO animals (grp,name) VALUES
    ('mammal','dog'),('mammal','cat'),
    ('bird','penguin'),('fish','lax'),('mammal','whale'),
    ('bird','ostrich');

SELECT * FROM animals ORDER BY grp,id;

Output:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

AUTO_INCREMENT values are reused if you delete the row with the largest AUTO_INCREMENT value in any group in this scenario (where the AUTO_INCREMENT column is part of a multiple-column index). This occurs even in MyISAM tables, where AUTO_INCREMENT values are often not reused.

If the AUTO_INCREMENT column is part of more than one index, MySQL creates sequence values from the index that starts with the AUTO_INCREMENT column, if one exists. If the animals table has the indexes PRIMARY KEY (grp, id) and INDEX (id), MySQL would skip the INDEX (id) when producing sequence values. As a result, the table would only have one sequence, rather than one for each grp value.


SQL Primary Key Create Table

A primary key in SQL is a single field or a set of fields that uniquely identifies a record. A NULL value cannot exist in any of the fields that make up the primary key. There can only be one main key in a table.

Each row in a table is uniquely identified by a primary key. A table's primary key can be made up of one or more columns. A composite key is created when a primary key is made up of numerous columns.

Because it makes no sense to utilise the NULL value to uniquely identify a record, a primary key cannot be NULL. As a result, the primary key column (or portion of a primary key column) cannot be NULL.

Primary keys can be provided when the table is formed (using CREATE TABLE) or when the table structure is changed (using ALTER TABLE). Setting main keys is an important part of database design because the primary key you choose has a big impact on the database's efficiency, usability, and extensibility.

We want to use as few columns as feasible when building the primary key. This is for the sake of both storage and performance. In a relational database, primary key information necessitates more storage, hence the more columns a primary key has, the more storage it necessitates. In terms of performance, fewer columns mean the database can process the primary key faster because there is less data, and it also implies table joins will be faster because primary keys and foreign keys are frequently used as joining conditions.

Note: When the primary key is supplied when the table is created, the primary key column(s) are immediately set to NOT NULL.

Syntax:

The syntax to create a primary key using the CREATE TABLE statement in SQL is:

CREATE TABLE table_name
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT constraint_name PRIMARY KEY (pk_col1, pk_col2, ... pk_col_n)
);

OR

CREATE TABLE table_name
(
  column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
  column2 datatype [ NULL | NOT NULL ],
  ...
);
  • table_name - The name of the table you want to change. This is the table to which a primary key should be added.
  • constraint_name - The name of the primary key.
  • column1, column2, ... column_n - The columns that make up the primary key.
  • pk_col1, pk_col2, ... pk_col_n - The columns that make up the primary key.

Example 1: Examples for specifying a primary key when creating a table:

Primary Key On One Column in MySQL:

CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));

Oracle:

CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

SQL Server:

CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name nvarchar(30),
First_Name nvarchar(30));

Example 2: Once you've connected to SQL Server, you'll usually begin by CREATING a new table with the field you want to utilise as your incremented primary key. We'll use the tried-and-true id field as an instance:

CREATE TABLE books (
  id              INT           NOT NULL,
  title           VARCHAR(100)  NOT NULL,
  primary_author  VARCHAR(100),
);

The difficulty is that we have no control over our id field. When creating a new record, we must not only manually enter an id value, but also run a query to ensure that the id value does not already exist (a near-impossibility when dealing with many simultaneous connections).

Example 3: Let's look at an example of how to use the Construct TABLE statement in SQL to create a primary key. We'll start with a simple one in which our primary key is only one column.

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  contact_name char(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

In this example, we've created a primary key on the suppliers table called suppliers_pk. It consists of only one column - the supplier_id column.

We could have used the alternate syntax and created this same primary key as follows:

CREATE TABLE suppliers
( supplier_id int CONSTRAINT suppliers_pk PRIMARY KEY,
  supplier_name char(50) NOT NULL,
  contact_name char(50)
);

Both of these syntaxes are valid when creating a primary key with only one field.

Example 4: If you want to establish a primary key that spans two or more columns, you may only use the first syntax, which places the primary key at the end of the CREATE TABLE statement.

CREATE TABLE contacts
( last_name VARCHAR(30) NOT NULL,
  first_name VARCHAR(25) NOT NULL,
  birthday DATE,
  CONSTRAINT contacts_pk PRIMARY KEY (last_name, first_name)
);

The contacts_pk primary key is created using a combination of the last_name and first_name columns in this example. In the contacts table, each combination of last_name and first_name must be unique.

Example 5: create a primary key for a table called product:

CREATE TABLE product (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
producer VARCHAR(100) NOT NULL,
price DECIMAL(7,2)
);

Discussion: You can use the keyword Main KEY at the end of the specification of a column to create a new table with that column declared as the primary key. In our illustration, we utilise a Build TABLE clause with the names of the columns and their data types enclosed in parenthesis to create the table product. We indicate PRIMARY KEY at the conclusion of the specification of this column because we want it to be the primary key of this table.


SQL Primary Key Name Convention

Naming Primary Keys :

Because primary keys are the only way for your table to be identified, it's crucial to name them carefully. It would be quite confusing if the primary key for several separate tables with different uses had the same name.

Use domain-specific names like StudentID or ID_Student instead than calling a primary key reflecting an identity number ID.

You may also use suffixes or prefixes with primary keys to differentiate them from the rest of the column names. Coordinates_PK or PK_Coordinates, for example.

Each record in a table is uniquely identified by a primary key. The following is the naming convention for a primary key:

The syntax for a Primary Key name should be:

"PK_ <Tablename>."
  • A "PK_" prefix should be added to each Primary Key name.
  • A table name's initial letter should be capitalised.
  • To signify plural, the last word of a table name should end with the character "s" (or "es").
  • If a table name has more than one word, capitalise the first letter of each term.

Example:

PK_Employees
PK_OrderDetails
PK_ProductDetails

SQL Primary Key Reset

To reset the primary key to 1 after deleting the data, use the following syntax

alter table yourTableName AUTO_INCREMENT=1;
truncate table yourTableName;

After doing the above two steps, you will get the primary key beginning from 1.

Step 1: To understand the above concept, let us create a table.

create table resettingPrimaryKeyDemo
UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY;

Step 2: Insert some records in the table using insert command. The query is as follows :

insert into resettingPrimaryKeyDemo values();
insert into resettingPrimaryKeyDemo values();
insert into resettingPrimaryKeyDemo values();
insert into resettingPrimaryKeyDemo values();

Step 3: Display all records from the table using select statement. The query is as follows :

select *from resettingPrimaryKeyDemo;

Output:

UserId
1
2
3
4

Here is the query to reset the primary key to 1

alter table resettingPrimaryKeyDemo AUTO_INCREMENT=1;

truncate table resettingPrimaryKeyDemo;

Check the records from the table. The query is as follows:

select *from resettingPrimaryKeyDemo;

Insert some records from the table using insert command. The query is as follows :

insert into resettingPrimaryKeyDemo values();
insert into resettingPrimaryKeyDemo values();
insert into resettingPrimaryKeyDemo values();

Now check the table primary key beginning from 1. The query is as follows :

select *from resettingPrimaryKeyDemo;

Output:

UserId
1
2
3

SQL Primary Key Rules

The rules of Primary Key are as follows:

  • The primary key column must have only one value.
  • There can only be one primary key per table.
  • There can't be any NULL values in the primary key column.
  • A pre-existing main key prevents you from inserting a new row.
  • For each table, there is only one primary key.

If the primary key is a column, it must have a unique value and must not be NULL. Each combination of values in these columns must be unique if the primary key consists of multiple columns.

If the table only has one column, a primary key can be defined as part of the column description. If the primary key is made up of many columns, it must be specified at the conclusion of the CREATE TABLE statement.

The data type of the main key column is restricted, for example, it cannot be BLOB, CLOB, ARRAY, or NCLOB.

When we build a table, we frequently include a primary key constraint. The ALTER TABLE statement can also be used to add a PRIMARY KEY constraint to an existing table that does not already have one. We can also change or remove an existing table's PRIMARY KEY constraint.


SQL Primary Key Start 1

By default, the AUTO_INCREMENT columns begin at 1. The value calculated automatically can never be less than 0.

To have a primary key begin at 1000, change your table to auto_increment with 1000 as the value.

Syntax:

alter table yourTableName auto_increment=1000;

Example 1: To understand the above syntax, let us first create a table. The query to create a table is as follows:

create table PrimaryKey1000Demo
ProductId int auto_increment,
PRIMARY KEY(ProductId);

Now here is the query that will update the primary key to start from 1000:

alter table PrimaryKey1000Demo auto_increment=1000;

The starting value has now been changed to 1000. Now let's add several records to see what the main key's beginning value is. The following is the query for inserting a record:

insert into PrimaryKey1000Demo values();

insert into PrimaryKey1000Demo values();
Query OK, 1 row affected (0.19 sec)

insert into PrimaryKey1000Demo values();
insert into PrimaryKey1000Demo values();

Display all records from the table using a select statement. The query is as follows:

select *from PrimaryKey1000Demo;

The following is the output displaying ProductID, our primary key to begin from 1000:

ProductId
1000
1001
1002
1003

Example 2: There can only be one AUTO INCREMENT column per table. It needs to be defined as a key (not necessarily the PRIMARY KEY or UNIQUE key). If a key has many columns, the AUTO INCREMENT column must be the first column in various storage engines (including the default InnoDB). Aria, MyISAM, MERGE, Spider, TokuDB, BLACKHOLE, FederatedX, and Federated are storage engines that allow the column to be moved.

CREATE TABLE animals (
     id MEDIUMINT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
 );

INSERT INTO animals (name) VALUES
    ('dog'),('cat'),('penguin'),
    ('fox'),('whale'),('ostrich');

SELECT * FROM animals;

Output:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | fox     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

SERIAL is an alias for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.

CREATE TABLE t (id SERIAL, c CHAR(1)) ENGINE=InnoDB;

SHOW CREATE TABLE t \G
*************************** 1. row ***************************
       Table: t

Create Table: CREATE TABLE `t` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `c` char(1) DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

SQL Primary Key Two Columns

A main key in a table uniquely identifies a row. A main key column cannot include NULL or duplicate values.

Why do we need a primary key?

To uniquely identify a table row, of course. We can use the AuhtorId field to uniquely identify two authors who have the identical first and last names.

In both of these cases, the primary key only has one column. It's the BookId column in the Books table, and it's AuthorId in the Authors table.

sql many to many relationship example

A third table is required, and the many-to-many relationship rows are stored in this table. This table is also known as the link, join, or junction table.

What is a composite primary key?

Composite primary keys are primary keys that are made up of two or more columns. When you have a many-to-many relationship between two tables, i.e. when several rows in one table are related with multiple rows in another table, this is a common real-world use case.

Customers and products tables, for example, have a many-to-many relationship since a customer can buy a lot of different things, and a product like an iPhone can be bought by a lot of different people.

Authors and books are another example. A single author can write a large number of books, while a single book can be written by several authors.

Relational database systems usually don't allow us to implement a direct many-to-many relationship between two tables.

sql server composite primary key example

In a real world this junction table would contain only the 2 foreign key columns and nothing else. In our example, those 2 columns are AuthorId and BookId.

Example 1: sql composite primary key with foreign key

The following is the code to create a composite primary key. In our example, we have 2 columns in the composite primary key. We can have more than 2 columns if we want to, just include another comma and your third column.

Create composite primary key while creating table

Create table Authors_Books
(
	AuthorId int not null foreign key references Authors(AuthorId),
	BookId int not null foreign key references Books(BookId)

	Constraint PK_Books_Authors Primary Key (AuthorId, BookId)
)
Go

Example 2: sql composite primary key example

A composite primary key is similar to a single-column primary key. All of the rules remain in effect. Null values are not permitted, and duplicates are not permitted. Values in a single column can be repeated, but they must be unique throughout all columns. In the composite primary key, null values are not permitted in any column.

Create table Authors
(
	AuthorId int primary key,
	FirstName nvarchar(20),
	LastName nvarchar(20),
	Gender nvarchar(20)
)
Go

Create table Books
(
	BookId int primary key,
	BookTitle nvarchar(50),
	Price int,
	Published bit
)
Go

Create table Authors_Books
(
	AuthorId int not null foreign key references Authors(AuthorId),
	BookId int not null foreign key references Books(BookId)

	Constraint PK_Books_Authors Primary Key (AuthorId, BookId)
)
Go

Insert into Authors values (1, 'Mark', 'Dunn', 'Male')
Insert into Authors values (2, 'Sara', 'Longhorn', 'Female')
Insert into Authors values (3, 'Jessica', 'Dale', 'Female')
Insert into Authors values (4, 'Steve', 'Wicht', 'Male')
Go

Insert into Books values (1, 'Learn SQL', 10, 1)
Insert into Books values (2, 'Learn C#', 20, 1)
Insert into Books values (3, 'Learn CSS', 15, 1)
Insert into Books values (4, 'Learn HTML', 20, 0)
Go

Insert into Authors_Books values (1, 1)
Insert into Authors_Books values (1, 2)
Insert into Authors_Books values (2, 1)

Example 3: SQL to create a table with COMPOSITE KEY and PRIMARY KEY constraint on multiple columns.

CREATE TABLE student
(Student_ID int NOT NULL,
Student_name varchar(255) NOT NULL,
City varchar(255),
Marks int
CONSTRAINT CompKey_ID_NAME PRIMARY KEY (Student_ID, Student_name));

Now, a new table called “student” is created and the fields “Student_ID” & “Student_name” is together defined as PRIMARY KEY.

CompKey_ID_NAME is the Constraint name. So, the combination of Student_ID and Student_name will be NOT NULL & UNIQUE.

Example 4: You want to create a primary key for a table in a database.

CREATE TABLE product (
name VARCHAR(100) NOT NULL,
producer VARCHAR(100) NOT NULL),
price DECIMAL(7,2),
PRIMARY KEY(name, producer)
);

Discussion: A PRIMARY KEY clause at the end of the column descriptions is another approach to create a primary key when creating a new table. In this scenario, one or more columns are passed as arguments to the PRIMARY KEY clause, and these columns comprise the table's primary key.

This method enables for the creation of primary keys with many columns. The primary key for the table product in this instance is made up of two columns: name and producer.


SQL Primary Key vs Foreign Key

Main Article :- Sql difference between PRIMARY KEY and FOREIGN KEY

In SQL Server, there are two types of keys: main and foreign keys, which appear to be the same but have different features and behaviours.

The uniqueness of the table is usually the focus of a primary key. Every row in the database is individually distinguished by a column or collection of columns. It means there should be no duplicate values. It also doesn't have a NULL value.

A foreign key is typically used to establish a link between two tables. The foreign key's primary function is to maintain data integrity between two distinct instances of an entity.

Primary Key Foreign Key
The uniqueness of the table is usually the focus of a primary key. It ensures that the value in each column is unique. A foreign key is typically used to establish a link between two tables.
A table can only have one primary key. In a table, we can have several foreign keys.
Null values are not allowed in the Primary Key. Multiple null values can be accepted by a foreign key.
It can uniquely identify a record in a database table. A foreign key is a table field that serves as the main key for another table.
A table's primary key cannot be removed unless all references to it are removed from other tables. Its value in the child table can be removed.
The temporary tables can be used to completely describe its restriction. Its limitations cannot be set on the global or local temporary tables.
The primary key is a clustered index by default, and data in the database table is physically organised in the clustered index sequence. Foreign keys, whether clustered or non-clustered, do not form an index by themselves. You can manually establish a foreign key index.
To ensure that data in a single column is unique, a primary key is used. A foreign key is a column or set of columns in a relational database table that connects data from different tables.
It identifies a record in a relational database table in a unique way. It relates to a table field that is the primary key for another table.
It constraint can be defined implicitly on temporary tables. It constraint cannot be defined on the temporary tables, either local or global.
It's made up of UNIQUE and Not Null restrictions. A table in a relational database can contain duplicate values.
It's either an existing table field or one generated by the database in a certain order. It's a column (or several columns) that refers to another table's column (usually the primary key).

Example 1: Creating Indexes on Tables

CREATE TABLE [dbo].[parent]
( 
[id] [int] IDENTITY NOT NULL, 
[name] [varchar](250) NOT NULL, 
CONSTRAINT [PK_dbo__parent] PRIMARY KEY NONCLUSTERED ([id])
)
 
CREATE TABLE [dbo].[child]
(
[id] [int] IDENTITY NOT NULL, [parent_id] [int] NULL, 
[name] [varchar](250) NOT NULL, 
CONSTRAINT [PK_dbo__child] PRIMARY KEY NONCLUSTERED ([id]), 
CONSTRAINT [FK_dbo__child__dbo__parent] FOREIGN KEY ([parent_id]) REFERENCES [dbo].[parent]([id])
)
 --Insert data
INSERT INTO [dbo].[parent] ([name]) VALUES ('parent1')
INSERT INTO [dbo].[child] ([parent_id], [name])VALUES(1, 'child 1')
INSERT INTO [dbo].[child] ([parent_id], [name])VALUES(NULL, 'child 2') 

--Select data 
SELECT * FROM [dbo].[child] 

In the example below, the record in [dbo].[child] is what would be referred to as an "orphan record". Think long and hard before doing this.

Droping Database Tables

IF EXISTS (SELECT * FROM [sys].[schemas] [sch] INNER JOIN [sys].[tables] [tbl] 
ON 
[sch].[schema_id] = [tbl].[schema_id] WHERE [sch].[name] = 'dbo' AND [tbl].[name] = 'child') 
DROP TABLE [dbo].[child]

IF EXISTS (SELECT * FROM [sys].[schemas] [sch] INNER JOIN [sys].[tables] [tbl] 
ON 
[sch].[schema_id] = [tbl].[schema_id] WHERE [sch].[name] = 'dbo' AND [tbl].[name] = 'parent') 
DROP TABLE [dbo].[parent]

Example 2: Primary key Example:

STUD_NO, as well as STUD_PHONE both, are candidate keys for relation STUDENT but STUD_NO can be chosen as the primary key (only one out of many candidate keys).

Foreign key Example:

STUD_NO in STUDENT_COURSE is a foreign key to STUD_NO in STUDENT relation.


SQL Primary Foreign Same Time

When it comes to students and the courses they've enrolled in, the problem of redundancy emerges when we try to keep all of the data in a single database.

To address this table, we create two tables: one for student information and another for department information. The student table contains information about students and the courses they have taken.

We also keep all of the department's information in the department table. In this case, the courseId is the Primary key for the department table and the Foreign key for the student table.

Let's look at how to create a table with a foreign key and a main key,

CREATE TABLE childTable (
    col1 int NOT NULL,
    col2 int NOT NULL,
    col3 int,
    ………...
    PRIMARY KEY (col1),
    FOREIGN KEY (col3) REFERENCES parentTable(parent_Primary_key)
);

SQL Primary and Unique Key

Main Article :- Sql difference between PRIMARY KEY and UNIQUE KEY

The PRIMARY Key and UNIQUE Key requirements are comparable in that they both enforce the column's uniqueness.

1.Primary Key

  • The primary key will not allow NULL entries and is used to make a table unique.
  • There can only be one primary key in a table.
  • When a primary key and the data in a database table are physically ordered in the clustered index's sequence, a clustered index is formed automatically.
  • As a Foreign Key, the primary key can be linked to other tables.
  • With the aid of the Auto Increment field, we may produce ID automatically. Auto Increment value is supported by the primary key.
  • On temporary tables and table variables, we can define primary key constraints.
  • The primary key value from the parent table that is utilised as a foreign key in the child table cannot be deleted. To delete, we must first delete the child table's primary key value.

2.Unique Key

  • NULL values are allowed for unique keys.
  • On a table, there might be multiple unique keys.
  • As a Foreign Key, Unique Constraint cannot be linked to another table.
  • When a primary key is defined, a clustered index is built automatically, whereas a unique key creates a non-clustered index.
  • Determines each row's unique identifier, which isn't the primary key.