Как изменить primary key sql

Primary Key refers to the column of a table that uniquely identifies a row in a table. It contains unique values and can not contain NULL values. For the purpose of the demonstration, we will be creating geeks for geeks table in a database called “geeks“.

Step 1: Creating the database 

The database is created in SQL Server using the following command 

Query:

CREATE DATABASE geeks;

Step 2: Using the Database

Use the below SQL statement to switch the database context to geeks:

Query:

USE geeks;

In SQL Server, the Primary key constraint is defined by the syntax:

Syntax:

CREATE TABLE tablename (
column_name datatype NOT
NULL PRIMARY KEY);

We can also define the primary key after making the table, but make sure the column has the NOT NULL constraint on it. The syntax for declaring primary key after defining the table:

Syntax:

Alter table table_name add  primary key (column_name);

To change the Primary key column in the SQL Server, follow these steps:

• Drop already defined primary key.
• Add a new column as the primary key.

Step 1: Table creation

We have the following geeks for geeks table in the geeks database

Query:

CREATE TABLE geeksforgeeks(
ID int PRIMARY KEY,
FIRSTNAME varchar(30),
LASTNAME varchar(30),
CITY varchar(18),
EmpID int NOT NULL
);

Step 2: Insert data into the table

Query:

INSERT INTO geeksforgeeks VALUES 
(1,'Romy', 'Kumari', 'Delhi',1900089),
(2,'Avinav', 'Pandey', 'Delhi',1909089),
(3,'Nikhil', 'Kalra', 'Punjab',1000089),
(4,'Mansi', 'Lal', 'Uttarpradesh',1905689),
(5,'Rinkle', 'Arora', 'Haryana',1900989),
(6,'Sakshi', 'Kumari', 'Delhi',1700089),
(7,'Soumya', 'Shriya', 'Bihar',1660089),
(8,'Mitu', 'Kumari', 'Rajasthan',1340089);

Step 3: Check the content of table

Query:

SELECT * FROM geeksforgeeks;

Output:

Check which column is set as Primary key, we can use following command:

Query:

SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE  ;

Output:

In the above figure, you can see ID column is the primary key. We can drop this primary key by using the CONSTRAINT_NAME mentioned in the image. From here copy CONSTRAINT_NAME to drop the primary key.

Command to drop primary key:

Query:

ALTER TABLE geeksforgeeks DROP CONSTRAINT PK__geeksfor__3214EC275032BA6D;

Add EmpID as new Primary key (always check NOT NULL constraint is specified, otherwise, an error will be thrown)

Query:

ALTER TABLE geeksforgeeks ADD PRIMARY KEY (EmpID);

Now check the primary key of the table using ‘SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ‘  command

Output:

In the above image, you can see EMPID is the primary key.

1. Update Primary Key in MySQL Tables
2. Drop the Existing Primary Key and Make a New Primary Key Using a Different Column in MySQL
3. Update the Number of Columns Involved in Constructing the Primary Key in MySQL
4. Update MySQL Primary Key By Changing Its Data Type

This tutorial teaches how to update the primary key in MySQL tables. We’ll be using the ALTER command to make any change in the primary key.

Update Primary Key in MySQL Tables

There are various scenarios where we can update the primary key in MySQL Tables. Let’s see each of them below.

1. Drop the existing primary key and make a new primary key using a different column.
2. Update the number of columns involved in constructing the primary key.
3. Change the data type of the primary key.

We can use the ALTER command that will be practically demonstrated later in this tutorial to change the primary key.

Drop the Existing Primary Key and Make a New Primary Key Using a Different Column in MySQL

Suppose we have a users table in the test database whose primary key is the ID attribute. For some reason, we want to drop it and construct a new primary key using the USERNAME column.

Create users Table:

#create a table named 'users' in 'test' database
CREATE TABLE `test`.`users` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `USERNAME` VARCHAR(45) NOT NULL,
  `EMAIL` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`));

Use the following query to check the table definition.

SHOW CREATE TABLE test.users;

It will show the following output; your results may differ if you use a different table.

Output:

To update the primary key from ID to the USERNAME field, we must drop the AUTO_INCREMENT from the ID attribute first; otherwise, it will generate an error.

Drop AUTO_INCREMENT for ID field:

# Disable foreign key check
SET FOREIGN_KEY_CHECKS=0;
# Modify the `ID` attribute
ALTER TABLE test.users MODIFY COLUMN ID INT NOT NULL;
# Enable foreign key check
SET FOREIGN_KEY_CHECKS=1;

Use SHOW CREATE TABLE test.users; to confirm that AUTO_INCREMENT is dropped.

Output:

Update Primary Key:

ALTER TABLE test.users DROP PRIMARY KEY, ADD PRIMARY KEY(USERNAME);

Use SHOW CREATE TABLE test.users; again to ensure that the primary key is USERNAME.

Output:

Update the Number of Columns Involved in Constructing the Primary Key in MySQL

We are moving ahead with the users table we created in the previous section. The primary key was the USERNAME column which can be easily checked using the following query.

SHOW CREATE TABLE test.users;

We use the ALTER command to update the primary key that will consist of two columns now, ID and USERNAME.

ALTER TABLE test.users DROP PRIMARY KEY, ADD PRIMARY KEY(ID, USERNAME);

We can confirm the updated primary key by using the following query.

SHOW CREATE TABLE test.users;

Output:

Update MySQL Primary Key By Changing Its Data Type

Here, we create another table and name it user where the primary key is the ID field of the INT type.

CREATE TABLE `test`.`user` (
  `ID` INT NOT NULL AUTO_INCREMENT,
  `USERNAME` VARCHAR(45) NOT NULL,
  `EMAIL` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`ID`));

We can use the following queries to update the data type from INT to BIGINT of an existing primary key.

SET FOREIGN_KEY_CHECKS=0;
ALTER TABLE test.users MODIFY COLUMN ID BIGINT NOT NULL AUTO_INCREMENT;
SET FOREIGN_KEY_CHECKS=1;

Confirm the changes by using a query which is given below.

SHOW CREATE TABLE test.user;

Output:

Updating the primary key in the MySQL table is not difficult before making relationships in the database but highly discouraged after establishing the relationships in the database for the following basic reasons.

1. If you got the idea to change the primary key in the running database, you probably have chosen the incorrect field for the primary key. So be extra careful about selecting the fields as a primary key.

2. You will be required to drop that record on which you are trying to change the primary key. You may have to lose all the relationships for that particular record.

   If you change the primary key, you have to add that record and create relationships again.

3. If you change the primary key from one column to three columns, the new primary key (consisting of three columns) must be used as a foreign key in all other related tables. Remember, this can impact storage, performance, and design.

4. Any change in the primary key is never encouraged within the database except if you re-construct the database during MIGRATION or FILE RE-ORGANIZATION. But, you have to be extra careful because these primary keys might be used as foreign keys in other tables.

Want to check how much you know SQL Server?