CHECK約束通過在表定義中設(shè)置規(guī)則，防止無效數(shù)據(jù)插入或更新。例如，確保價格非負、員工年齡在18-65歲之間、薪資大于零，可使用CHECK(price >= 0)、CHECK(age >= 18 AND age

When you want to make sure the data in your SQL tables stays clean and meaningful, CHECK constraints are a straightforward way to do it. They let you define rules right in the table definition so invalid data doesn’t sneak in during inserts or updates.

What a CHECK constraint does

A CHECK constraint makes sure that values going into a column (or across multiple columns) meet certain conditions. If the condition returns false, the database blocks the operation — simple as that.

For example, if you have a "prices" table and you never want negative prices allowed, you can add:

CHECK (price >= 0)

This stops anyone from accidentally inserting -5 as a price. It’s not just numbers either — you can use them for strings, dates, or even logic that spans multiple fields.

How to set one up

You can create a CHECK constraint when making a new table or later on using an ALTER statement.

At table creation time:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 18 AND age <= 65),
    salary DECIMAL CHECK (salary > 0)
);

Here, we're ensuring that all employees are between 18 and 65 years old and that no zero or negative salaries get stored.

After the table exists:

ALTER TABLE employees
ADD CONSTRAINT check_salary_positive CHECK (salary > 0);

Some databases like MySQL support CHECK constraints but may ignore them unless configured properly, so always double-check your DBMS behavior.

Common use cases people miss

There are a few places where CHECK constraints really shine but often go unused.

• Validating status codes: If your app uses short codes like 'A', 'P', 'C' to mean active, pending, canceled — you can enforce only those letters:

  CHECK (status IN ('A', 'P', 'C'))

• Date ranges: You might want start dates before end dates in events or contracts:

  CHECK (start_date <= end_date)

• Email formats: While full regex validation is usually better handled in code, basic checks like requiring an '@' symbol can be done:

  CHECK (email LIKE '%@%')

  Just keep in mind that complex validations (like checking against another table) usually need triggers or application-level logic instead.

  A couple gotchas to remember

  • They don't apply retroactively: Adding a CHECK constraint won’t validate existing rows. So if you’re adding one to an existing table, make sure current data already follows the rule.

  • Case sensitivity depends on your system: For string comparisons, some databases treat 'A' and 'a' differently; others don’t. Be aware of collation settings.

  • Use named constraints: It's easier to manage them later if you give them names like check_age_range rather than relying on auto-generated ones.

  So if you're trying to prevent bad data at the source, CHECK constraints are worth using — they're fast, easy, and effective for many common scenarios.

  基本上就這些。

  以上是使用SQL檢查約束來執(zhí)行數(shù)據(jù)驗證規(guī)則。的詳細內(nèi)容。更多信息請關(guān)注PHP中文網(wǎng)其他相關(guān)文章！