SQL PRIMARY KEY

SQL PRIMARY KEY Data Type and Characteristics

Constraints define the properties of columns and tables in a database. A PRIMARY KEY constraint characteristically:

• Must have a unique value for each row: No two rows can possess the same PRIMARY KEY.
• Cannot contain NULL values: A PRIMARY KEY must always have a value.

There are no specific data types for PRIMARY KEYS; they can be any data type that supports unique values. However, some commonly used data types include:

• INTEGER: When using INTEGER, the PRIMARY KEY usually takes the form of a unique, auto-incrementing number.
• CHAR, VARCHAR: These data types are used when the PRIMARY KEY needs to be a string or a combination of letters and numbers.
• UUID: A universally unique identifier (UUID) helps generate primary keys when distributed databases require globally unique values.

SQL PRIMARY KEY Example: Implementing in Database Design

Suppose you are working on a database for an online bookstore, and you need to create a table to store information about each book. The steps to implementing a PRIMARY KEY in this context are:

1. Create the table, define columns and data types
2. Specify the PRIMARY KEY column and constraint
3. Insert data into the table, ensuring unique PRIMARY KEY values

CREATE TABLE books (
    book_id INT AUTO_INCREMENT,
    title VARCHAR(255),
    author VARCHAR(255),
    publication_year INT,
    PRIMARY KEY (book_id)
);

INSERT INTO books (title, author, publication_year)
VALUES ('To Kill a Mockingbird', 'Harper Lee', 1960),
       ('1984', 'George Orwell', 1949),
       ('The Catcher in the Rye', 'J.D. Salinger', 1951);

By enforcing a PRIMARY KEY on the 'book_id' column, you guarantee that each record in the 'books' table has a unique and non-NULL identifier. This makes it easy to search, update, and delete records, as well as create relationships with other tables in the database.

Creating Tables with SQL PRIMARY KEY

Creating a table with SQL PRIMARY KEY is essential to maintain a well-structured, organized, and efficient database storage system. In this section, we will explore the syntax and guidelines for creating tables with PRIMARY KEYS and discuss how to add or modify PRIMARY KEYs in existing tables.

Create Table SQL with PRIMARY KEY: Syntax and Guidelines

To create a table with SQL PRIMARY KEY, you need to understand the correct syntax and guidelines. The PRIMARY KEY constraint is specified during table creation, and its basic syntax is as follows:

CREATE TABLE table_name (
    column_name1 data_type PRIMARY KEY,
    column_name2 data_type,
    ...
);

You can also use an alternative syntax with the CONSTRAINT keyword:

CREATE TABLE table_name (
    column_name1 data_type,
    column_name2 data_type,
    ...
    CONSTRAINT constraint_name PRIMARY KEY (column_name1)
);

When creating a table with a PRIMARY KEY, follow these guidelines:

• Choose the right column as the PRIMARY KEY: It should uniquely identify each row in the table and not contain NULL values.
• Consider using an auto-incrementing INTEGER or a UUID (universally unique identifier) data type for the PRIMARY KEY column.
• If necessary, you can define a composite PRIMARY KEY consisting of two or more columns to ensure uniqueness across multiple attributes.

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    birth_date DATE
);

Adding and Modifying SQL PRIMARY KEY in Existing Tables

In some cases, you may need to add or modify PRIMARY KEY constraints in existing tables. Let's discuss the two main scenarios:

Adding a PRIMARY KEY to an existing table

To add a PRIMARY KEY constraint to an existing table, you must first ensure that the column(s) you plan to use as the PRIMARY KEY don't contain duplicate or NULL values. Then, use the ALTER TABLE command to create the constraint:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name1);

ALTER TABLE students
ADD CONSTRAINT pk_student_id PRIMARY KEY (student_id);

Modifying an existing PRIMARY KEY

To modify an existing PRIMARY KEY, you must first remove the constraint using the ALTER TABLE command and DROP CONSTRAINT keyword. Then, create a new PRIMARY KEY constraint on the desired column using the same ALTER TABLE command:

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

ALTER TABLE table_name
ADD CONSTRAINT new_constraint_name PRIMARY KEY (new_column_name);

ALTER TABLE students
DROP CONSTRAINT pk_student_id;

ALTER TABLE students
ADD CONSTRAINT pk_new_student_id PRIMARY KEY (new_student_id);

When adding or modifying PRIMARY KEYs, always ensure that your chosen column(s) meet the PRIMARY KEY criteria and maintain data integrity within your database.

SQL PRIMARY KEY and FOREIGN KEY Relationships

Understanding the relationship between SQL PRIMARY KEY and FOREIGN KEY is crucial when working with relational databases. PRIMARY KEYS uniquely identify each row in a table, while FOREIGN KEYS maintain connections to related records in other tables. Establishing well-defined relationships ensures data integrity and enables the retrieval of meaningful information from multiple tables through complex queries.

Establishing SQL PRIMARY KEY and FOREIGN KEY Constraints

PRIMARY and FOREIGN KEY constraints play a significant role in defining the relationships between tables in a relational database. To establish these relationships, follow a series of steps:

1. Create tables with PRIMARY KEYs: Define each table with a uniquely identifying attribute (usually an auto-incrementing integer, VARCHAR, or UUID) and enforce a PRIMARY KEY constraint on that attribute.
2. Create FOREIGN KEY columns: Add appropriate columns to the table that will hold the FOREIGN KEYs, referencing the PRIMARY KEYs from related tables.
3. Define FOREIGN KEY constraints: For each of the FOREIGN KEY columns, specify a FOREIGN KEY constraint that enforces referential integrity and establishes the linkage between the tables.

A typical FOREIGN KEY constraint definition follows this syntax:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (column_name)
REFERENCES referenced_table_name(referenced_column_name);

For example, consider an e-commerce database with tables for 'customers' and 'orders'. Each customer can place multiple orders, and each order belongs to a single customer. This implies a one-to-many relationship between the 'customers' and 'orders' tables. To achieve this, you need to:

1. Create a PRIMARY KEY on the 'customer_id' column in the 'customers' table.
2. Create a FOREIGN KEY on the 'customer_id' column in the 'orders' table, referencing the 'customer_id' PRIMARY KEY in the 'customers' table.

SQL PRIMARY KEY and FOREIGN KEY Example in Data Modelling

Create the 'customers' table with a PRIMARY KEY on the 'customer_id' column:

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    email VARCHAR(255) UNIQUE,
    registered_date DATE
);

Create the 'orders' table, including a column for the FOREIGN KEY reference:

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    order_total DECIMAL(10,2)
);

Establish the FOREIGN KEY constraint, linking the 'orders' table to the 'customers' table:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer_id FOREIGN KEY (customer_id)
REFERENCES customers(customer_id);

This FOREIGN KEY constraint ensures that every order in the 'orders' table is linked to a valid customer in the 'customers' table. You have now successfully modelled a one-to-many relationship between the 'customers' and 'orders' tables using SQL PRIMARY KEY and FOREIGN KEY constraints. These well-defined relationships enable you to perform complex queries retrieving information about customers and their orders, thus making the most of the relational database structure.