MySQL Tutorial: part 3

Constraints

NOT NULL

The NOT NULL constraint is a column constraint that forces the values of a column to non-NULL values only.

column_name data_type NOT NULL;

• Add a NOT NULL constraint to an existing column

In this case, you use the following steps:

1. Check the current values of the column.
2. Update the NULL values to non-null values.
3. Add the NOT NULL constraint

PRIMARY KEY

A primary key is a column or a set of columns that uniquely identifies each row in the table. You must follow the rules below when you define a primary key for a table:

• A primary key must contain unique values. If the primary key consists of multiple columns, the combination of values in these columns must be unique.

• A primary key column cannot contain NULL values. It means that you have to declare the primary key column with the NOT NULL attribute. If you don’t, MySQL will force the primary key column as NOT NULL implicitly.

• A table has only one primary key.

A primary key column often has the AUTO_INCREMENT attribute that generates a unique sequence for the key automatically.

id PRIMARY KEY

or ..

PRIMARY KEY(id_1,id_2)

• Defining MySQL PRIMARY KEY constraints using ALTER TABLE statement

ALTER TABLE table_name
ADD PRIMARY KEY(primary_key_column);

• PRIMARY KEY vs. UNIQUE KEY vs. KEY

A KEY is a synonym for INDEX. You use the KEY when you want to create an index for a column or a set of columns that is not the part of a primary key or unique key.

A UNIQUE index creates a constraint for a column whose values must be unique. Unlike the PRIMARY index, MySQL allows NULL values in the UNIQUE index. A table can also have multiple UNIQUE indexes.

FOREIGN KEY

A foreign key is a field in a table that matches another field of another table. A foreign key places constraints on data in the related tables, which enables MySQL to maintain referential integrity.

A foreign key can be a column or a set of columns. The columns in the child table often refer to the primary key columns in the parent table.

A table may have more than one foreign key, and each foreign key in the child table may refer to a different parent table.

• The following syntax illustrates how to define a foreign key in a child table in CREATE TABLE statement.

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

• Adding a foreign key to a table MySQL adding foreign key syntax

ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action;

• Dropping MySQL foreign key

ALTER TABLE table_name 
DROP FOREIGN KEY constraint_name;

• Disabling foreign key checks

To disable foreign key checks, you use the following statement:

SET foreign_key_checks = 0;

you can enable it using the statement below:

SET foreign_key_checks = 1;

UNIQUE

The UNIQUE constraint is either column constraint or table constraint that defines a rule that constrains values in a column or a group of columns to be unique.

To add the UNIQUE constraint to a column, you use the following syntax:

CREATE TABLE table_1(
    column_name_1  data_type UNIQUE,
);

Or you can define the UNIQUE constraint as the table constraint as follows:

CREATE TABLE table_1(
 
   ...
   column_name_1 data_type,
   column_name_2 data type,
   ...
   UNIQUE(column_name_1,column_name_2)
);

If you want to assign a specific name to a UNIQUE constraint, you use the CONSTRAINT clause as follows:

CREATE TABLE table_1(
   ...
   column_name_1 data_type,
   column_name_2 data type,
   ...
   CONSTRAINT constraint_name UNIQUE(column_name_1,column_name_2)
);

To remove a UNIQUE constraint, you use can use DROP INDEX or ALTER TABLE statement as follows:

DROP INDEX index_name ON table_name;

ALTER TABLE table_name
DROP INDEX index_name;

Adding a UNIQUE constraint to a table that already exists

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE (column_list);

Managing Database Index

A database index, or just index, helps speed up the retrieval of data from tables. When you query data from a table, first MySQL checks if the indexes exist, then MySQL uses the indexes to select exact physical corresponding rows of the table instead of scanning the whole table.

A database index is similar to an index of a book. If you want to find a topic, you look up in the index first, and then you open the page that has the topic without scanning the whole book.

Notice that all primary key columns are in the primary index of the table automatically.

Creating MySQL Index

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
USING [BTREE | HASH | RTREE] 
ON table_name (column_name [(length)] [ASC | DESC],...)

Removing Indexes

DROP INDEX index_name ON table_name

UNIQUE INDEX

To create a UNIQUE index, you use the CREATE UNIQUE INDEX statement as follows:

CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...);

The following statement illustrates how to create a unique constraint when you create a table.

CREATE TABLE table_name(
...
   UNIQUE KEY(index_column_,index_column_2,...) 
);

If you want to add a unique constraint to an existing table, you can use the ALTER TABLE statement as follows:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name UNIQUE KEY(column_1,column_2,...);

• UNIQUE Index & NULL

MySQL considers NULL values as distinct values. Therefore, you can have multiple NULL values in the UNIQUE index.

Refenrence

Basic MySQL Tutorial