In MySQL, constraints are rules defined on columns in a table to enforce data integrity and ensure that data satisfies certain conditions. There are several types of constraints that can be applied to columns:
1. NOT NULL: Ensures that a column cannot contain NULL values.
CREATE TABLE table_name (
column_name INT NOT NULL,
...
);
2. UNIQUE: Ensures that all values in a column are unique.
CREATE TABLE table_name (
column_name INT UNIQUE,
...
);
3. PRIMARY KEY: Combines the NOT NULL and UNIQUE constraints. Each table can have only one primary key, and it uniquely identifies each row in the table.
CREATE TABLE table_name (
column_name INT PRIMARY KEY,
...
);
4. FOREIGN KEY: Enforces referential integrity by defining a relationship between two tables. The values in the foreign key column must match values in the referenced column of the parent table or be NULL.
CREATE TABLE child_table (
...
foreign_key_column INT,
FOREIGN KEY (foreign_key_column) REFERENCES parent_table(parent_column)
);
5. CHECK: Specifies a condition that must be met by the values in a column.
CREATE TABLE table_name (
column_name INT CHECK (column_name > 0),
...
);
6. DEFAULT: Sets a default value for a column if no value is specified during INSERT operations.
CREATE TABLE table_name (
column_name INT DEFAULT 0,
...
);
These constraints ensure data consistency and integrity in the database, preventing invalid or inconsistent data from being stored. It's essential to carefully consider and define constraints when designing database schemas to maintain data quality and accuracy.