sql primary key multiple column with code examples

SQL is a critical part of software development that allows the storage and manipulation of data. One of its essential features is the primary key constraint, which establishes a unique identifier for each record in a table. In some cases, a primary key may be composed of several columns.

A primary key with multiple columns is a powerful tool that lets you create unique combinations of data where a single column would not suffice. Multi-column primary keys are useful in scenarios such as the implementation of complex business rules, data aggregation, and reporting. Here, we will explore how to create and use multiple-column primary keys in SQL with code examples.

Creating a Multi-Column Primary Key

The syntax for creating a multi-column primary key is similar to that of a simple primary key. You need to specify a list of columns enclosed in parenthesis after the CONSTRAINT keyword. The following SQL statement demonstrates how to create a primary key composed of two columns:

CREATE TABLE emp_details (
  emp_id INT(6),
  emp_code VARCHAR(10),
  emp_name VARCHAR(30),
  emp_dept VARCHAR(30),
  emp_salary INT(10),
  PRIMARY KEY (emp_id, emp_code)
);

In the above code, a table emp_details is created with five columns, including two columns named emp_id and emp_code. The primary key is created using both of these columns with the PRIMARY KEY keyword.

Inserting Data with a Multi-Column Primary Key

When inserting data into a table that has a multi-column primary key, the values of each column constituting the primary key must be unique. Consider the following example of an emp_details table:

emp_id | emp_code | emp_name     | emp_dept     | emp_salary 
------------------------------------------------------------
1001    E001      John Doe       Sales          50000
1001    E002      Jane Smith     IT             45000
1002    E003      Mark Johnson   Finance        55000
1003    E004      James Madison Human Resources 60000

Here, the primary key constraint is created using both the emp_id and emp_code columns. Notice that the first two rows have the same emp_id value, which is valid because there is a unique emp_code value for each record. The third row has a different combination of emp_id and emp_code, making it valid for the primary key constraint. The fourth row also has a combination of emp_id and emp_code that is unique from the previous rows, making it a valid record.

Querying Data with a Multi-Column Primary Key

Queries in SQL that involve tables with a multi-column primary key are similar to queries in tables with a simple primary key. For instance, to select all records from an emp_details table with the primary key columns, you use the following syntax:

SELECT emp_id, emp_code, emp_name, emp_dept, emp_salary
FROM emp_details;

This statement retrieves all the data in the emp_details table. To select only specific records, you apply various filtering methods, such as using the WHERE clause, as shown below:

SELECT emp_name, emp_salary
FROM emp_details
WHERE emp_dept = 'Sales';

This query retrieves the emp_name and emp_salary columns for all records in the emp_details table where emp_dept equals Sales.

Using ALTER TABLE with a Multi-Column Primary Key

In SQL, the ALTER TABLE statement enables you to modify an existing table, including altering its primary key constraints. To add a new column to an existing table with a multi-column primary key, use the following syntax:

ALTER TABLE emp_details
ADD COLUMN emp_address VARCHAR(50);

In the above code, the emp_address column is added to the emp_details table. However, adding a new column to a table with a multi-column primary key requires that you update the primary key constraint to include the new column. In this case, you need to drop the existing primary key and recreate it, including the new column:

ALTER TABLE emp_details
DROP PRIMARY KEY,
ADD PRIMARY KEY(emp_id, emp_code, emp_address);

This code drops the existing primary key (emp_id, emp_code) and recreates it to include the emp_address column.

Conclusion

SQL provides an effective way of managing and manipulating data in different tables. Primary keys serve the purpose of providing a unique identifier to each record in a table. A multi-column primary key can be implemented to help identify combinations of data that cannot be identified with a single column. In this article, we explored the creation of a multi-column primary key, inserting data into it, querying data, and using the ALTER TABLE statement to modify its constraints. With the knowledge gained, you can now confidently create tables with multi-column primary keys and manipulate them to suit your needs.

In this article, we discussed the concept of a multi-column primary key in SQL and its advantages. A primary key is a database constraint that ensures unique and identifiable rows in a table. It consists of one or more columns that establish a unique identifier for each record.

A multi-column primary key, as the name suggests, consists of multiple columns and represents a combination of fields that uniquely identify a record. This is particularly useful in applications where using a single column as a primary key is not sufficient to identify a unique record.

The syntax of creating a multi-column primary key is similar to that of a simple primary key, but you need to specify a list of columns in parenthesis after the PRIMARY KEY keyword.

CREATE TABLE customers (
    customer_id INT,
    customer_name VARCHAR(50),
    address VARCHAR(50),
    city VARCHAR(50),
    state VARCHAR(50),
    zip VARCHAR(10),
    PRIMARY KEY (customer_id, zip)
);

The above SQL statement creates a table named customers with six columns. The primary key constraint is established using both the customer_id and zip columns.

Inserting data into a table with a multi-column primary key requires that the values in the columns defining the primary key must be unique. For example, in the customers table with the customer_id and zip columns constituting the primary key, inserting two records with the same customer_id but different zip values would be valid. However, inserting two records with the same customer_id and zip values would violate the primary key constraint.

INSERT INTO customers (customer_id, customer_name, address, city, state, zip)
VALUES (1, 'John Doe', '123 Main St', 'New York', 'NY', '10001');

INSERT INTO customers (customer_id, customer_name, address, city, state, zip)
VALUES (1, 'Jane Smith', '456 Elm St', 'Los Angeles', 'CA', '90001');

-- The following insert would cause an error
INSERT INTO customers (customer_id, customer_name, address, city, state, zip)
VALUES (1, 'Mark Johnson', '789 Maple St', 'Chicago', 'IL', '10001');

Queries for tables with multi-column primary keys are used in the same way as tables with simple primary keys.

SELECT customer_name, address, city, state, zip
FROM customers
WHERE customer_id = 1 AND zip = '10001';

The above code retrieves all records in the customers table with a customer_id of 1 and a zip of 10001.

In addition, using the ALTER TABLE statement, you can modify an existing table with a multi-column primary key.

ALTER TABLE customers
ADD COLUMN phone_number VARCHAR(15);

ALTER TABLE customers
DROP PRIMARY KEY,
ADD PRIMARY KEY (customer_id, zip, phone_number);

The above code modifies the existing customers table to add the phone_number column. It then alters the existing primary key constraint to include all the columns with the ALTER TABLE statement.

In conclusion, multi-column primary keys in SQL servers as a helpful data modeling tool to identify and uniquely define records in a table. They can improve the functionality and efficiency of an application that relies on complex data models. It is important to remember that a multi-column primary key's columns must be unique to be operational.

Popular questions

  1. What is a multi-column primary key in SQL?
    A multi-column primary key consists of two or more columns in a SQL database table which are used together to uniquely identify each row. It is often used when a single column cannot be used as a unique identifier.

  2. What is the syntax for creating a multi-column primary key?
    The syntax for creating a multi-column primary key is similar to that of a simple primary key, but you need to specify a list of columns in parenthesis after the PRIMARY KEY keyword. For example:

CREATE TABLE customers (
    customer_id INT,
    customer_name VARCHAR(50),
    address VARCHAR(50),
    city VARCHAR(50),
    state VARCHAR(50),
    zip VARCHAR(10),
    PRIMARY KEY (customer_id, zip)
);
  1. Can primary keys be composed of more than two columns?
    Yes, primary keys can be composed of more than two columns.

  2. What is the purpose of a primary key constraint?
    A primary key constraint ensures that each row in a table is uniquely identifiable. This helps maintain data integrity and prevents duplicate rows.

  3. How do you modify a primary key constraint in SQL?
    To modify a primary key constraint in SQL, you can use the ALTER TABLE statement. For example, to add a new column to an existing table with a multi-column primary key, you can first add the column using ALTER TABLE and then modify the primary key using the DROP PRIMARY KEY, ADD PRIMARY KEY syntax.

ALTER TABLE customers
ADD COLUMN phone_number VARCHAR(15);

ALTER TABLE customers
DROP PRIMARY KEY,
ADD PRIMARY KEY (customer_id, zip, phone_number);

Tag

"CompositeKey"

I am a driven and diligent DevOps Engineer with demonstrated proficiency in automation and deployment tools, including Jenkins, Docker, Kubernetes, and Ansible. With over 2 years of experience in DevOps and Platform engineering, I specialize in Cloud computing and building infrastructures for Big-Data/Data-Analytics solutions and Cloud Migrations. I am eager to utilize my technical expertise and interpersonal skills in a demanding role and work environment. Additionally, I firmly believe that knowledge is an endless pursuit.

Leave a Reply

Your email address will not be published. Required fields are marked *

Related Posts

Begin typing your search term above and press enter to search. Press ESC to cancel.

Back To Top