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
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
Quiz
1.What is a primary key in SQL?
Answer: a. A unique identfier for each row in a table
2.What type of data must a primary key in SQL contain?
Answer: a. A unique value
3. What is the purpose of using a primary key in SQL?
Answer: b. To identify rows in a table
4. What is the syntax for creating a primary key in SQL?
Answer: d. ALTER TABLE table_name PRIMARY KEY
Related Tutorials to watch
Top Articles toRead
Read