Bytes

PRIMARY KEY in SQL

Module - 5 Constraints in SQL
PRIMARY KEY in SQL

Overview

A SQL primary key could be a column (or combination of columns) in a table that uniquely recognizes each row. It may be a special type of constraint, and is the most imperative characteristic of any relational database table. Primary keys must contain unique values and cannot contain null values. They are used to reference a row in another table, form relationships, and enforce data integrity.

Points to remember for the primary key

  1. Primary keys must contain unique values.
  2. Primary keys should not contain null values.
  3. Primary keys should not contain duplicate values.
  4. Primary keys should be short and concise.
  5. Primary keys should be kept from being changed or modified.
  6. Primary keys should be stable over time.
  7. Primary keys should be chosen wisely.
  8. Primary keys should be simple and intuitive.

The main advantage of the primary key:

The primary key's main advantage is ensuring data integrity by uniquely identifying each row in a table. This helps to prevent duplicate records from being created and ensures that each record can easily be retrieved and updated. Primary keys also help improve queries' performance as they allow for faster searching and indexing.

SQL primary key for one column:

ALTER TABLE table_name
ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

This can be an SQL statement utilized to make a primary key for a single column in a table. The statement includes a column named "id" of type INT (integer) and sets it as the primary key, meaning it'll be utilized to identify each row within the table interestingly. The AUTO_INCREMENT keyword causes the column's value to increment automatically each time a new row is inserted into the table.

Example

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INT
);

This statement creates a new " students " table with four columns: student_id, first_name, last_name, and age. The student_id column is set to be the PRIMARY KEY, meaning that it must contain a unique value for each row in the table.

SQL primary key for multiple columns:

Syntax

CREATE TABLE table_name (
    column1 INT NOT NULL,
    column2 INT NOT NULL,
    column3 INT NOT NULL,
    PRIMARY KEY (column1, column2, column3)
);

This creates a table with three columns, all of which are integers and are not allowed to be null. It then creates a primary key constraint on all three columns, ensuring that all the values in the three columns together are unique.

example

CREATE TABLE students (
    student_id INT NOT NULL,
    class INT NOT NULL,
    period INT NOT NULL,
    PRIMARY KEY (student_id, class, period)
);

This creates a table of students, with each row containing the student's ID, the class they are in, and the period of the day. The primary key ensures that each student is unique for a given class and period.

SQL primary key on ALTER TABLE

Primary key on one column:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);

This query is utilized to include a primary key to an existing table. The 'table_name' indicates the name of the table to which the primary key will be included. The 'constraint_name' indicates the title of the constraint to be included. The 'column_name' indicates the column's title on which the primary key will be included.

Example

ALTER TABLE orders
ADD CONSTRAINT orders_pk PRIMARY KEY (orderID);

This query is used to add a primary key to the 'orders' table. The primary key is named 'orders_pk' and is applied to the 'orderID' column. This primary key will ensure that no two orders have the same orderID.

Primary key on multiple columns:

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY(column1, column2);

This statement adds a primary key constraint to a table and specifies multiple columns that should be part of the primary key. The constraint_name is an optional name for the constraint. The columns listed in the parentheses are the columns that will be part of the primary key.

Example

ALTER TABLE students ADD CONSTRAINT student_pk PRIMARY KEY(student_id, student_name);

This statement adds a primary key constraint named "student_pk" to the table "students" and specifies that the columns "student_id" and "student_name" should be part of the primary key. This means that each combination of values for these two columns must be unique in the table.

How to DROP a PRIMARY KEY constraint?

To drop a primary key constraint, use the following SQL syntax:

ALTER TABLE table_name DROP CONSTRAINT constraint_name;

For example:

ALTER TABLE student_table DROP CONSTRAINT pk_student_table_id;

This SQL statement will drop the primary key constraint on the student_table, identified as pk_student_table_id. After executing this statement, the primary key constraint will no longer be enforced on the student_table.

Conclusion

A primary key in SQL may be a column or combination of columns in a table that interestingly distinguishes each row. It implements data integrity, prevents duplicate records, and permits speedier searching and indexing. An essential key must contain one-of-a-kind and non-null values and be steady over time and can be made utilizing the Alter TABLE or Create TABLE statement. It can be dropped using the ALTER TABLE statement as well.

Key takeaways

  1. A primary key is a unique identifier used to identify a record in a database table uniquely.
  2. It is typically composed of one or more columns uniquely identifying a row in a table.
  3. A primary key column must contain unique values and cannot contain null values.
  4. Primary keys are used to enforce entity integrity as they ensure that no two rows have the same values.
  5. In SQL, the primary key is specified using the PRIMARY KEY constraint.

Quiz

1.What is a primary key in SQL? 

  1. A unique identifier for each row in Table 
  2.  A value must be the same for each row in table 
  3.  A special type of key used to join tables 
  4.  A type of constraint used to ensure data integrity

Answer: a. A unique identfier for each row in a table

2.What type of data must a primary key in SQL contain? 

  1. A unique value 
  2. A non-unique value 
  3. A string of characters 
  4. A numerical value

Answer: a. A unique value

3. What is the purpose of using a primary key in SQL? 

  1. To ensure data consistency 
  2. To identify rows in the table 
  3. To enforce business rules 
  4. To improve performance

Answer: b. To identify rows in a table

4. What is the syntax for creating a primary key in SQL?

  1. ALTER TABLE table_name ADD CONSTRAINT PRIMARY KEY 
  2. UPDATE table_name SET PRIMARY KEY 
  3. CREATE TABLE table_name PRIMARY KEY 
  4. ALTER TABLE table_name PRIMARY KEY

Answer: d. ALTER TABLE table_name PRIMARY KEY

Online SQL Compiler
Open Compiler
Recommended Courses
Masters in CS: Data Science and Artificial Intelligence
Course
20,000 people are doing this course
Join India's only Pay after placement Master's degree in Data Science. Get an assured job of 5 LPA and above. Accredited by ECTS and globally recognised in EU, US, Canada and 60+ countries.
Certification in Full Stack Data Science and AI
Course
20,000 people are doing this course
Become a job-ready Data Science professional in 30 weeks. Join the largest tech community in India. Pay only after you get a job above 5 LPA.

AlmaBetter’s curriculum is the best curriculum available online. AlmaBetter’s program is engaging, comprehensive, and student-centered. If you are honestly interested in Data Science, you cannot ask for a better platform than AlmaBetter.

avatar
Kamya Malhotra
Statistical Analyst
Fast forward your career in tech with AlmaBetter

Vikash SrivastavaCo-founder & CPTO AlmaBetter

Vikas CTO

Related Tutorials to watch

Top Articles toRead

AlmaBetter
Made with heartin Bengaluru, India
  • Official Address
  • 4th floor, 133/2, Janardhan Towers, Residency Road, Bengaluru, Karnataka, 560025
  • Communication Address
  • 4th floor, 315 Work Avenue, Siddhivinayak Tower, 152, 1st Cross Rd., 1st Block, Koramangala, Bengaluru, Karnataka, 560034
  • Follow Us
  • facebookinstagramlinkedintwitteryoutubetelegram

© 2023 AlmaBetter