mysql keys, data constraints in database tables (PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT, CHECK, FOREIGN KEY) composite primary key constraints, unique constraints, default constraints, check constraints, foreign key constraints. cascade

Let’s first look at the explanation of the results of the desc xxx table in mysql:

  • The “Field” column displays the name of each field (i.e., column) of the table.
  • The “Type” column shows the data type of each field.
  • The “Null” column indicates whether the field can be NULL. If the field does not allow NULL, this column displays “NO”; otherwise, it displays “YES”.
  • The “Key” column shows whether the field is defined as a key. “PRI” indicates that the field is the primary key of the table.
  • The “Default” column shows the default value for the field. In this example, the default value for all fields is NULL.
  • The “Extra” column displays any additional information. In this example, no fields have additional information.

Let’s get to the point.

Article directory

  • Field constraints in MySQL8 database table
    • introduction
    • PRIMARY KEY (Each table can only have one primary key, and the value of the primary key must be unique and cannot be NULL; the primary key can use a single field, or two or more fields can be combined into a composite primary key)
      • About the primary key (Primary Key) primary key constraints of the table
      • About Composite Primary Key Composite Primary Key Constraints
    • Unique constraint (UNIQUE) (guarantee the uniqueness of field data; multiple unique constraints can exist in a table)
      • The difference between primary key constraints and unique constraints
    • Not NULL constraint (NOT NULL) (ensures that a column (field) in the table cannot have a NULL value)
    • Default constraint (DEFAULT) (if the inserted data does not specify a value for a column (field), the default value will be used)
      • Set default constraints and set non-null constraints
    • Check constraints (CHECK) (used to limit the range of values of a column (field))
      • Setting a check constraint does not mean that a non-null constraint is set by default
      • Note: Check constraints are not supported before MySQL 8.0.16
    • FOREIGN KEY (FOREIGN KEY) (with other tables)
      • Common uses of foreign key constraints
        • 1. Ensure data integrity
        • 2. Create relationships
          • Example
        • 3. Cascade operation (determines how the system automatically updates or deletes the related foreign key value when a primary key value referenced by a foreign key is changed or deleted)
          • Cascade operation expansion

Field constraints in MySQL8 database table

Introduction

When building a database model, an important task is to ensure data integrity and accuracy. To achieve this goal, MySQL provides a variety of constraint types to limit modifications to data in the table. These constraints include primary key constraints, unique constraints, non-null constraints, default constraints, check constraints, and foreign key constraints.

PRIMARY KEY (Each table can only have one primary key, and the value of the primary key must be unique and cannot be NULL; the primary key can use a single field or a combination of two or more fields. Composite primary key)

Primary key constraints define unique identifiers for data in a table. Each table can only have one primary key, and the value of the primary key must be unique and cannot be NULL.

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

In this example, ID is the primary key of the Employees table.

About the primary key constraints of the table

The primary key of a table is one or more fields (columns) that uniquely identify each record in the database table. The important property of a primary key is that it must contain a unique value and cannot be NULL. In database design, it is very important to choose the appropriate primary key because the primary key is not only used to uniquely identify records, but is often referenced by other tables as foreign keys.

For example, assume there is a table named “Employees” that contains four fields: “ID”, “LastName”, “FirstName” and “Age”. The “ID” field can be set as the primary key since each employee has a unique ID.

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

In this example, the ID column is the primary key, which means it must contain a unique value (i.e., each employee must have a different ID) and cannot be NULL (i.e., each employee must have a ID).

Note: The field used as the primary key is automatically added with the NOT NULL constraint. If you don’t write mysql, it will be automatically added for you

About Composite Primary Key (Composite Primary Key) composite primary key constraints

In addition to using a single field as the primary key, you can also use two or more fields to form a composite primary key (Composite Primary Key). This is common in scenarios where two or more fields are required to uniquely identify a record.

For example, in an order detail table, there may be multiple order IDs corresponding to multiple product IDs. At this time, the two columns OrderID and ProductID can be combined as the primary key.

CREATE TABLE OrderDetails (
    OrderID int NOT NULL,
    ProductID int NOT NULL,
    Quantity int,
    PRIMARY KEY (OrderID, ProductID)
);

In this example, OrderID and ProductID are combined into a composite primary key. This means that no two records can have the same OrderID and ProductID at the same time.

Unique constraint (UNIQUE) (guarantee the uniqueness of field data; a table can have multiple unique constraints)

A unique constraint guarantees that all data in the table has unique values. A table can have multiple unique constraints, but each unique constraint must have a different value.

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    UNIQUE(ID)
);

In this example, the ID column has a unique constraint, so no two rows can have the same ID value.

The difference between primary key constraints and unique constraints

Primary key constraints and unique constraints do have some similarities, but there are also some important differences:

  1. NULL value: Primary key fields do not allow NULL values to be inserted, while unique constraint fields can accept NULL values (unless explicitly declared as NOT NULL).

  2. Quantity: In a table, there can be only one primary key (although this primary key can be composed of multiple fields), and a table can have multiple unique constraints.

  3. Purpose: A primary key is usually how a table’s records are referenced by other tables (i.e., a foreign key). The unique constraint only ensures that the value of a certain field is unique.

Here is an example showing both:

CREATE TABLE Example (
    ID int NOT NULL,
    Value int,
    PRIMARY KEY (ID),
    UNIQUE (Value)
);

In this example, ID is the primary key, cannot be NULL, and must be unique. Value has a unique constraint, so its value must also be unique, but can be NULL.

NOT NULL constraint (ensures that a column (field) in the table cannot have a NULL value)

The non-null constraint ensures that a column in the table cannot have NULL values.

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
);

In this example, the ID and LastName columns have non-null constraints, so they cannot accept NULL values.

Default constraint (DEFAULT) (if the inserted data does not specify a value for a column (field), the default value will be used)

Default constraints are used to insert default values into columns. If no value is specified for the column, the default value is used.

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int DEFAULT 30,
);

In this example, if no Age value is specified when inserting a new row, the default value of 30 is used.

Set default constraints and set non-null constraints

Note: When setting default constraints, you can also set non-empty constraints at the same time. Such as: 

Age int NOT NULL DEFAULT 30;

Check constraint (CHECK) (used to limit the range of values of a column (field))

Check constraints are used to limit the range of values in a column. If you try to insert a value that violates a check constraint, MySQL will reject the operation.

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age >= 18)
);

In this example, the Age column has a check constraint that requires that all employees must be greater than or equal to 18 years old.

Setting a check constraint does not mean that a non-null constraint is set by default

As in the above example, although the CHECK constraint ensures that the value of the Age field must be greater than or equal to 18, it does not explicitly prohibit NULL values.

In the SQL standard, NULL is a special value that means “unknown” or “not applicable”. When comparing NULL values, the result is always unknown (that is, NULL is neither greater than, less than, nor equal to any value). Therefore, if the Age field contains NULL, the CHECK (Age >= 18) constraint will not be able to determine whether the condition is met, so it will allow NULL values.

If you want to ensure that the Age field is neither NULL nor greater than or equal to 18, you can set it like this:

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int NOT NULL CHECK (Age >= 18)
);

In this example, the NOT NULL constraint ensures that the Age field cannot be NULL, while the CHECK constraint ensures that the Age field The value must be greater than or equal to 18.

Note: MySQL does not support check constraints before version 8.0.16

Prior to MySQL 8.0.16, although you could include CHECK constraints in the syntax, MySQL did not actually check them. That is, even if the data violates the CHECK constraint, MySQL will allow the data to be inserted or updated into the table.

Starting from MySQL version 8.0.16, MySQL begins to support CHECK constraints and will throw an error when the data violates the constraints.

For example, assume you run the following command on MySQL 8.0.15 or earlier:

CREATE TABLE Employees (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int CHECK (Age >= 18)
);

INSERT INTO Employees (ID, LastName, FirstName, Age)
VALUES (1, 'Doe', 'John', 17);

Although 17 is less than the minimum age of 18 specified by the CHECK constraint, MySQL will still allow this INSERT statement to succeed.

However, if you run the same command on MySQL 8.0.16 or later, MySQL will refuse to execute the INSERT because the value of Age violates the CHECK constraint. statement and throw an error.

Note that when writing code involving CHECK constraints, you should take into account the version of MySQL being used to ensure that the code behaves as expected.

I was so scared that I quickly looked at my mysql version:

8.0.34, not bad.

Foreign key constraints (FOREIGN KEY) (with other tables)

Foreign key constraints are used to prevent operations on the table that would damage the integrity of the relational data. A table can have multiple foreign keys.

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    EmployeeID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (EmployeeID) REFERENCES Employees(ID)
);

In this example, EmployeeID is a foreign key to the Orders table, which references the ID column of the Employees table. If you try to insert a value that does not exist in the ID column of the Employees table, or delete a value in the Employees table that is Orders ID referenced by the code> table, MySQL will reject this operation.

Common uses of foreign key constraints

Foreign Key (Foreign Key) is an important concept in the database, used to link two tables and constrain the data in one of the tables. A foreign key is defined in one table and refers to the primary key of another table.

Here are some common uses of foreign key constraints:

1. Ensure data integrity

By using foreign key constraints, you ensure that data integrity is maintained between two related tables. If you try to insert a value that does not exist or delete a value that is referenced by another record, the database will throw an error.

2. Create relationships

Foreign keys allow you to create relationships between tables. For example, you might have an Orders table and a Customers table, where the CustomerID column in the Orders table is < Foreign key to the ID column of the code>Customers table. This creates a relationship from order to customer.

Example

Here is an example of how to create the Customers table and the Orders table and establish a relationship between them:

First, we create a Customers table with two fields: ID and Name. Where ID is the primary key.

CREATE TABLE Customers (
    ID int NOT NULL,
    Name varchar(255),
    PRIMARY KEY (ID)
);

Next, we create an Orders table with three fields: OrderID, Product and CustomerID. Where OrderID is the primary key and CustomerID is the foreign key, referencing the ID field of the Customers table.

CREATE TABLE Orders (
    OrderID int NOT NULL,
    Product varchar(255),
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(ID)
);

Now we have two tables, and they are connected by the CustomerID field (a foreign key in the Orders table) and the ID field (Customers table) is associated. This means every order can be traced back to a single customer. Also, if you try to enter a CustomerID that does not exist in the Customers table, or delete a CustomersOrders table /code> table records, the database will throw errors to maintain data integrity.

3. Cascade operation (determines how the system automatically updates or deletes the related foreign key value when a primary key value referenced by a foreign key is changed or deleted)

When you define a foreign key, you can specify cascade operations (such as CASCADE, SET NULL, SET DEFAULT, NO ACTION, RESTRICT). This determines how when you change or delete a primary key value that is referenced by a foreign key, the system automatically updates or deletes the related foreign key value.

For example, assume we have two tables: Orders and Customers. We can set a foreign key constraint in the Orders table so that CustomerID refers to the ID column of the Customers table, and When a record in the Customers table is deleted, delete all related orders in the Orders table:

CREATE TABLE Customers (
    ID int NOT NULL,
    Name varchar(255),
    PRIMARY KEY (ID)
);

CREATE TABLE Orders (
    OrderID int NOT NULL,
    CustomerID int,
    Product varchar(255),
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(ID) ON DELETE CASCADE
);

In this example, if you delete a customer, all of that customer’s orders will also be automatically deleted to maintain data consistency.

Cascading operation expansion

(To be updated)

Reference article:

  • MySQL Official Documentation, Constraints
  • MySQL Tutorial, MySQL Constraints: Enforce Data Integrity