Constraints
In MySQL, constraints are rules applied to columns in a table to enforce data integrity,
define relationships, and ensure the accuracy and reliability of the data.
CREATE TABLE table_name (
Column_name1 datatype constraint,
Column_name2 datatype constraint,
Column_name3 datatype constraint,
.........
);
UNIQUE
A UNIQUE constraint ensures that all values in a column are UNIQUE across the table,
but it accepts NULL.
During Table Creation (with Column Name):
CREATE TABLE table_name (
column_name data_type UNIQUE
);
During Table Creation (at End):
CREATE TABLE table_name (
column1 data_type,
column2 data_type,
UNIQUE (column1)
);
Adding to Existing Table:
ALTER TABLE table_name
ADD UNIQUE (column_name);
Dropping UNIQUE Constraint:
ALTER TABLE table_name
DROP INDEX index_name;
NOT NULL
This constraint ensures that a column cannot contain NULL values.
During Table Creation:
CREATE TABLE table_name (
column_name data_type NOT NULL
);
Adding to Existing Table:
ALTER TABLE table_name
MODIFY column_name data_type NOT NULL;
PRIMARY KEY
A primary key uniquely identifies each record in a table. Only one primary key is allowed
per table, and it cannot contain NULL values.
During Table Creation (with Column Name):
CREATE TABLE table_name (
column_name data_type PRIMARY KEY
);
During Table Creation (at End):
CREATE TABLE table_name (
column1 data_type,
column2 data_type,
PRIMARY KEY (column1)
);
Adding to Existing Table:
ALTER TABLE table_name
ADD PRIMARY KEY (column_name);
Dropping PRIMARY KEY:
ALTER TABLE table_name
DROP PRIMARY KEY;
FOREIGN KEY
A FOREIGN KEY is used to establish a relationship between two tables.
It links a column in one table (the child table) to the PRIMARY KEY of another table (the
parent table), ensuring referential integrity.
● Parent Table: The table containing the referenced key (usually the primary key).
● Child Table: The table containing the foreign key that references the parent
table.
During Table Creation:
without named constraint :
CREATE TABLE child_table (
column_name data_type,
FOREIGN KEY (column_name) REFERENCES parent_table (parent_column)
);
With Named Constraint :
CREATE TABLE child_table (
column_name data_type,
CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES
parent_table (parent_column)
);
Adding to Existing Table:
ALTER TABLE child_table
ADD CONSTRAINT fk_name
FOREIGN KEY (child_column) REFERENCES parent_table (parent_column);
Dropping FOREIGN KEY:
ALTER TABLE child_table
DROP FOREIGN KEY fk_name;
DEFAULT
A default constraint assigns a default value to a column if no value is specified.
During Table Creation:
CREATE TABLE table_name (
column_name data_type DEFAULT default_value
);
Modifying Existing Table:
ALTER TABLE table_name
MODIFY column_name data_type DEFAULT default_value;
Dropping DEFAULT:
ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;
CHECK
The check constraint allows you to specify a condition that each value in a column must
satisfy.
During Table Creation:
without named constraint :
CREATE TABLE table_name (
column_name data_type CHECK (condition)
);
With Named Constraint :
CREATE TABLE table_name (
column_name data_type,
CONSTRAINT constraint_name CHECK (condition)
);
Adding to Existing Table:
ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);
Dropping CHECK Constraint:
ALTER TABLE table_name
DROP CHECK constraint_name;
AUTO_INCREMENT
This constraint is typically used with a primary key to automatically generate a unique
integer value for new rows.
Setting AUTO_INCREMENT:
CREATE TABLE table_name (
column_name INT AUTO_INCREMENT PRIMARY KEY
);
Changing Starting Value:
ALTER TABLE table_name AUTO_INCREMENT = value;
Changing Increment Step:
SET @@auto_increment_increment = value;
