You are currently viewing SQL 1.48 SQL PRIMARY KEY

SQL 1.48 SQL PRIMARY KEY

Understanding SQL PRIMARY KEY Constraint


In SQL, the PRIMARY KEY constraint is a fundamental concept used to uniquely identify each record in a table. It ensures that every row in a table has a unique identifier, and no two rows can have the same value for the primary key column(s). This tutorial will walk you through the basics of using PRIMARY KEY in SQL, including syntax, usage, and examples.

1. Syntax of PRIMARY KEY Constraint:


The syntax for defining a PRIMARY KEY constraint in SQL is as follows:

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    ...
);

Explanation:

  • CREATE TABLE statement is used to create a new table in the database.
  • table_name is the name of the table you want to create.
  • column1 is the name of the column you want to designate as the primary key.
  • datatype specifies the data type of the column.
  • PRIMARY KEY keyword is used to define the primary key constraint.

2. Creating a Table with PRIMARY KEY


Let’s create a simple table named employees with columns id, name, and email. We’ll designate the id column as the primary key.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50)
);

Explanation:

  • In this example, we created a table named employees with three columns: id, name, and email.
  • The id column is defined as type INT and designated as the primary key using the PRIMARY KEY constraint.
  • name and email columns are defined with data type VARCHAR(50) to store text values.

3. Inserting Data into the Table


Now, let’s insert some sample data into the employees table:

INSERT INTO employees (id, name, email) VALUES
(1, 'John Doe', 'john@example.com'),
(2, 'Jane Smith', 'jane@example.com');

Explanation:

  • We use the INSERT INTO statement to add new records to the employees table.
  • The values for id, name, and email are provided for each record being inserted.

4. Retrieving Data from the Table


To retrieve data from the employees table, you can use the SELECT statement:

SELECT * FROM employees;

Output:

| id |   name     |       email        |
|----|------------|--------------------|
| 1  | John Doe   | john@example.com  |
| 2  | Jane Smith | jane@example.com  |

Explanation:

  • The SELECT * statement retrieves all columns from the employees table.
  • The result displays the data currently stored in the table.

5. Handling Primary Key Violations


If you try to insert a record with a duplicate primary key value, it will result in an error. Let’s demonstrate this by trying to insert a record with the same id as an existing record:

INSERT INTO employees (id, name, email) VALUES (1, 'Mark Johnson', 'mark@example.com');

Output (Error):

Error: Duplicate entry '1' for key 'PRIMARY'

Explanation:

  • The error message indicates that the primary key constraint is violated because there’s already a record with id value of 1 in the employees table.

Conclusion


In this tutorial, you learned the basics of using the PRIMARY KEY constraint in SQL to ensure uniqueness of records in a table. By defining a primary key, you can enforce data integrity and facilitate efficient data retrieval operations. Practice creating tables with primary keys and inserting data to solidify your understanding of this important concept in SQL.

Leave a Reply