Constraints in MySQL
Constraints are the rules that are defined on a database or table.
AUTO_INCREMENT
As the name suggests, AUTO_INCREMENT automatically increments a value of a column starting value with 1 if no value is found.
CREATE TABLE table_name ( column1 datatype AUTO_INCREMENT, column2 datatype, ... columnN datatype );
CREATE TABLE with NOT NULL CONSTRAINT
CREATE TABLE table_name ( column1 datatype NOT NULL, column2 datatype, ... columnN datatype );
Adding NULL CONSTRAINT to an existing column
ALTER TABLE table_name MODIFY column datatype NOT NULL;
DROPPING NOT NULL CONSTRAINT
ALTER TABLE table_name MODIFY column datatype NULL;
CREATE TABLE with UNIQUE CONSTRAINT
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... columnN datatype, UNIQUE(column1) );
Adding UNIQUE CONSTRAINT to an existing column
ALTER TABLE table_name ADD UNIQUE(column_name);
You can also name your constraint while defining them. Using this method you can add it to multiple columns.
ALTER TABLE table_name ADD CONSTRAINT uc UNIQUE(column_name1, column_name2);
DROPPING UNIQUE CONSTRAINT
ALTER TABLE table_name DROP INDEX column_name;
Since unique constraint is also an index you can drop it using above syntax.
ORIf you have named your constraint then use the following syntax:
ALTER TABLE table_name DROP INDEX uc;
Here uc is constraint name that you defined it while creating it.
CREATE TABLE with CHECK CONSTRAINT
Before we begin with CHECK CONSTRAINT let me tell you one important thing. CHECK CONSTRAINT don't work as expected in MySQL. There is some bug
Its written in MySQL manual that 'The CHECK clause is parsed but ignored by all storage engines'.
Here I'm explaining check clause for the completeness of this tutorial.
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... columnN datatype, CHECK(condition) );
Adding CHECK CONSTRAINT to an existing column
ALTER TABLE table_name ADD CHECK(condition);
You can also name your constraint while defining them. Using this method you can add it to multiple columns.
ALTER TABLE table_name ADD CONSTRAINT chk CHECK (condition);
DROPPING CHECK CONSTRAINT
ALTER TABLE table_name DROP CHECK chk;
CREATE TABLE with DEFAULT CONSTRAINT
CREATE TABLE table_name ( column1 datatype DEFAULT some_value, column2 datatype, ... columnN datatype, );
Adding DEFAULT CONSTRAINT to an existing column
ALTER TABLE table_name ALTER column_name SET DEFAULT value;
If value is a string then put it inside single or double quotes.
DROPPING DEFAULT CONSTRAINT
ALTER TABLE table_name ALTER column_name DROP DEFAULT;
CREATING INDEX
CREATE [UNIQUE] INDEX index_name ON table_name(column1, column2,...);
DROPPING INDEX
ALTER TABLE table_name DROP INDEX index_name;
Primary key constraints and foreign key constraints are discussed next.