Primary Keys and Unique Constraints
In the concept of Relational Database Management Systems (RDBMS), primary keys and unique constraints are used to insert user data into database tables with uniqueness. Primary keys and unique constraints maintain the integrity of newly inserted table data by blocking or stopping duplicate table data information and invalid database table records. Both primary keys and unique constraints restrict the repetition of data inserted into the table, thus strictly following the concept of uniqueness in both database tables. However, the working methods of primary keys and unique constraints are different, and when and where they should be used depends entirely on the database user.
Let’s understand Primary Keys and Unique Constraints in Relational Database Management Systems (RDBMS) better.
Primary Key Concept in (RDBMS).
In an SQL database management system, a primary key is a column field or a collection of individual table data fields in a table that uniquely validates or identifies each row in the database user-created table. It is considered one of the most important constraints followed by database users when designing a database. It ensures that the primary key values in two rows of a database table are not the same or identical.
Features of Primary Keys in Database Management Systems.
- Data Uniqueness – The primary key value in a database user-created table must be unique or different for each row in the table. The primary key ensures that the primary key values in any two rows of a table are not the same or identical.
- Not Null Value – The primary key column in a designed database table cannot have any NULL values. A valid table data value must be inserted for the primary key in every table record.
- Single Primary Key – Remember that a user-created database table can only have one primary key, but this primary key can be generated from one or more table columns using a composite key.
RDBMS Primary Key Syntax.
CREATE TABLE table_name (
column1 datatype PRIMARY KEY,
column2 datatype,
column3 datatype,
…
);
Or, the syntax for a composite primary key.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
PRIMARY KEY (column1, column2, column3)
);
Example of a Primary Key.
CREATE TABLE employee (
employee_id INT PRIMARY KEY,
f_name VARCHAR(120),
l_name VARCHAR(120),
emp_age INT
);
In this example.
- Here, in this example, `employee_id` is defined as a primary key. This means that every employee in the employee table must have a unique `employee_id` as the primary key, and no `employee_id` record data value can be NULL in the inserted employee table.
Unique Constraint in (RDBMS).
In an SQL database management system, a unique constraint ensures that all values in a column (or combination of columns) are distinct within the table. It validates that no two rows can have the same value in the constrained column, but unlike a primary key, it allows NULL values, depending on the RDBMS database.
Features of Unique Constraints in Database Management Systems.
- Data Uniqueness – A unique constraint in a database table ensures that each value in the table column is unique across all table rows. Remember that no two rows in the table can have the same value in the constrained column.
- Nullability – A unique constraint in a database table allows one or more NULL values in the table column. This table behavior may vary across different database systems. For example, SQL Server software allows multiple NULL values in a table column with a unique constraint. However, some other database software only allows a single NULL value in a table column.
- Multiple Unique Constraints – Unlike a primary key in a database table, multiple unique constraints can be created in a table. This means that uniqueness can be applied to different table columns or sets of column values.
Syntax for a Unique Constraint.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
CONSTRAINT constraint_name UNIQUE (column1)
);
Alternatively, a database user can define a unique constraint directly on a table column.
CREATE TABLE table_name (
column1 datatype UNIQUE,
column2 datatype
);
Example of a Unique Constraint.
CREATE TABLE employee (
employee_id INT PRIMARY KEY,
f_name VARCHAR(120),
l_name VARCHAR(120),
emp_email VARCHAR(140) UNIQUE,
salary INT
);
In this example.
- Here, a primary key is defined on employee_id.
- The emp_email column in the employee table is defined or restricted by a unique constraint, meaning that each entered user email in the existing database table must be unique throughout the table. Unlike a primary key, this may allow NULL values, depending on the specific RDBMS database.
Composite Primary Key Concept in RDBMS.
In a database management system, a composite primary key is similar to a primary key, where the created database table has two or more table columns. A composite primary key is useful in a database when no single column in the created database table can uniquely validate or identify a table row, but a combination of table columns can.
Example of a composite primary key in RDBMS.
CREATE TABLE product_item (
order_id INT,
product_id INT,
prod_quantity INT,
price INT,
PRIMARY KEY (order_id, product_id)
);
In this example.
- the combination of order_id and product_id uniquely validates or identifies each table row in the product_item table.
- This determines that in the existing table, a single product item can be associated with multiple orders, while each product in a single order must be unique or different.
Composite Unique Constraint Concept in RDBMS.
Similar to a composite primary key in a database management system, a composite unique constraint in a database table applies uniqueness to a combination of table columns. The main difference is that it does not necessarily require one of the columns in the existing table to be a primary key.
Example of a composite unique constraint in RDBMS.
CREATE TABLE employee (
employee_id INT PRIMARY KEY,
f_name VARCHAR(120),
l_name VARCHAR(120),
emp_email VARCHAR(130),
CONSTRAINT unique_emp_email UNIQUE (emp_email)
);
In This Example.
- the emp_email column must have unique values and is restricted by its unique value. Unlike a primary key, the emp_email column can be defined with a unique constraint without being a primary key.
Main difference Between Primary Key and Unique Constraint
| Key Feature | Primary Key features | Unique Constraint features |
| Key Uniqueness | Primary Key used to created database table Enforces uniqueness for each row in the table record or column data. | Unique Constraintfeaturesenable Enforces uniqueness for each row in the user insert table data. |
| Data Nullability | User created table Primary Key Does not allow NULL values in any table. | Unique ConstraintAllows some of the software one or more NULL values depending on the RDBMS software it allows. |
| Number of Constraints | Primary Key in A user created database table can have only one primary key. | With Unique ConstraintA user created table can have multiple unique constraints element. |
| Key Purpose | Primary Key used to Uniquely identifies each row in the user created database table. | Unique Constrainthelp us toEnsures data in a column or combination is unique. |
| Key Usage | Primary Key generally used for the table column(s) that uniquely identify each table records (e.g, emp_ID). | Unique ConstraintisUsed for enforcing uniqueness on other columns (e.g, emp_email, username, etc). |
Conclusion of Primary Keys and Unique Constraints.
- In Relational Database Management Systems (RDBMS), both primary keys and unique constraints play an important role in ensuring data integrity in user-created database tables and enforcing uniqueness in database table columns.
- A primary key created in a database table is used to uniquely validate or identify each row in the table, and the table records must have unique and non-null values.
- A unique constraint in a database table enforces uniqueness, but it allows for NULL values and can be applied to multiple columns in a database table.
