关于东城区新侨饭店东侧路 采取交通管理措施的通告

SQL constraints are used to ensure the accuracy and integrity of data, mainly including: 1. The NOT NULL constraint requires that the field must have a value to prevent null values; 2. The UNIQUE constraint ensures that the field value is unique, allowing a NULL but prohibiting duplication of non-null values; 3. The CHECK constraint forces the field value to meet the specified conditions, such as age ≥18; 4. The PRIMARY KEY constraint is a unique identifier, and has both NOT NULL and UNIQUE characteristics; 5. The FOREIGN KEY constraint maintains the integrity of references between tables to prevent invalid foreign key references; 6. The DEFAULT constraint provides a default value for the field, and is automatically filled if not specified during insertion. Together, these constraints guarantee data reliability and avoid duplicate, invalid or siloed records, which are best practices in database design.

Sure! In SQL, constraints are rules applied to columns in a table to ensure the accuracy, reliability, and integrity of the data. Here are the most common types of constraints, with clear explanations and examples:

1. NOT NULL Constraint

This constraint ensures that a column cannot have a NULL value . It forces the field to always contain data.

Use case: You don't want a user's email or name to be missing in a users table.

 CREATE TABLE Users (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL
);

Now, if someone tries to insert a row without a name or email, the database will reject it.

2. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column are different . It allows one NULL (in most databases), but no duplicate non-NULL values.

Use case: You want to make sure no two users have the same email address.

 CREATE TABLE Users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

You can also apply UNIQUE to multiple columns together (a composite unique constraint):

 UNIQUE (first_name, last_name) -- Ensures no two people have the same full name

Note: PRIMARY KEY automatically enforces uniqueness and NOT NULL.

3. CHECK Constraint

The CHECK constraint makes sure that all values in a column satisfy a specific condition .

Use case: You want to ensure ages are positive or salaries are above a minimum.

 CREATE TABLE Employees (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 18),
    salary DECIMAL CHECK (salary > 0)
);

You can also use CHECK with multiple columns:

 CHECK (end_date > start_date)

If a row violates the condition, the insert or update is rejected.

4. PRIMARY KEY Constraint

This is a combination of NOT NULL and UNIQUE . It uniquely identifies each row in a table.

 id INT PRIMARY KEY
-- or
PRIMARY KEY (id)

A table can have only one PRIMARY KEY, but it can span multiple columns (composite key).

5. FOREIGN KEY Constraint

This constraint ensures referential integrity between two tables. It links a column (or group of columns) in one table to a PRIMARY KEY in another.

Use case: Ensuring every order references an existing customer.

 CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

This prevents inserting an order with a customer_id that doesn't exist.

6. DEFAULT Constraint

This sets a default value for a column when no value is specified during insertion.

 status VARCHAR(10) DEFAULT 'pending'

If you insert a row without specifying status , it will automatically be set to 'pending' .

Summary of Key Constraints:

• NOT NULL : No missing values.
• UNIQUE : All values are distinct.
• PRIMARY KEY : Unique identifier (NOT NULL UNIQUE).
• FOREIGN KEY : Ensures valid references between tables.
• CHECK : Validates data against a condition.
• DEFAULT : Provides a fallback value.

These constraints help maintain clean, reliable data and prevent common errors like duplicates, invalid entries, or orphaned records. Using them appropriately is a best practice in database design.

Basically, just pick the right constraint based on what rule you want to enforce.

The above is the detailed content of Can you explain the different types of constraints in SQL, such as NOT NULL, UNIQUE, and CHECK?. For more information, please follow other related articles on the PHP Chinese website!