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