How does MySQL handle constraints?

Let us understand how MySQL handles constraints-

MySQL helps us handle transactional tables (rollback is allowed) and non-transactional tables (rollback is not allowed) rollback). This is why MySQL handles constraints differently than other DBMSs. In a non-transactional database, if an error occurs while inserting or updating multiple rows, there is no way to roll back. This situation must be handled in the right way.

MySQL Server generates errors for queries that detect errors when parsing the statement that needs to be executed. Once an error is detected, it attempts to recover from the error while executing the statement. This is not currently done for all statements.

MySQL can stop a statement in the middle or recover from it and continue when an error occurs. By default, the server recovers from this and continues.

Foreign keys allow cross-reference of data across tables, and foreign key constraints help ensure that this scattered data remains consistent.

Foreign key references

MySQL supports ON UPDATE and ON DELETE foreign key references in CREATE TABLE and ALTER TABLE statements.

Reference operations

The available reference operations are - RESTRICT, CASCADE, SET NULL and NO ACTION.

NO ACTION is the default reference operation.

ENUM

The ENUM value must be the value listed in the column definition, or the equivalent internal number. The value cannot be an error value (0 or empty string). For a column defined as ENUM('a','b','c'), values ​​other than 'a', 'b' or 'c', such as '', 'd' or 'ax ' are considered Invalid and rejected.

The above is the detailed content of How does MySQL handle constraints?. For more information, please follow other related articles on the PHP Chinese website!