Constraints in MySQL

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.

If 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.