Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How MySQL Deals with Constraints?
Let us understand how MySQL deals with constraints −
MySQL helps us work with transactional tables (which permit rollback) and with non-transactional tables (which don’t permit rollback). This is the reason why handling constraints is different in MySQL in comparison to other DBMS. In non-transactional database, if an error occurs while inserting or updating many rows, it can’t be rolled back. This case has to be handled in the right manner.
MySQL Server produces an error for queries which it detects to be errors, when parsing a statement that needs to be executed. Once the error has been detected, it tries to recover from the errors while executing the statement. This is not done for all statements as of now.
MySQL can either stop the statement in the middle or recover from it and continue when an error occurs. By default, the server recovers from it and continues.
Foreign keys allow cross-referencing across data across tables, and foreign key constraints help ensure that this spread-out data remains consistent.
Foreign Key References
MySQL supports ON UPDATE and ON DELETE foreign key references in CREATE TABLE and ALTER TABLE statements.
Referential Actions
The referential actions available are- RESTRICT, CASCADE, SET NULL, and NO ACTION.
NO ACTION is the default referential action.
ENUM
An ENUM value must be one that is listed in the column definition, or the internal numeric equivalent. The value cannot be an error value (0 or the empty string). For a column that is defined as ENUM('a','b','c'), values other than ‘a’, ‘b’, or ‘c’, such as '', 'd', or 'ax' are considered invalid and are rejected.
109 Views
