MySQL Constraints

1. NOT NULL constraint ensures that the field cannot accept a NULL value. This means if NOT NULL constraint is applied on a column then you cannot insert a new row in the table without adding a value for that column.
2. UNIQUE constraint ensures that each row for a column must have a unique value.
3. PRIMARY KEY constraint marks the corresponding field as the table's primary key. Primary key is a combination of NOT NULL and UNIQUE.
4. DEFAULT Constraint specifies the default value for the columns.
5. AUTO_INCREMENT AUTO_INCREMENT tells MySQL to automatically assign a value to this field by incrementing the previous value by 1. Only available for numeric fields.
6. FOREIGN KEY A Foreign Key is a database key that is used to link two tables together. The FOREIGN KEY constraint identifies the relationships between the database tables by referencing a column, in the Child table that contains the foreign key, to the PRIMARY KEY column, in the Parent table.

The foreign key places constraints on data in the related tables, which allows MySQL to maintain referential integrity. There are three types of relationships that can exist between two tables:

1. one-to-one
2. one-to-many
3. many-to-many

In the orders table has a customerNumber that exists in the customers table. Multiple rows in the orders table can have the same customerNumber.

Syntax:

• CASCADE: if a row from the parent table is deleted or updated, the values of the matching rows in the child table automatically deleted or updated.
• RESTRICT: if a row from the parent table has a matching row in the child table, MySQL rejects deleting or updating rows in the parent table.

CREATE TABLE categories(
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
) ENGINE=INNODB;

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
        REFERENCES categories(categoryId)
) ENGINE=INNODB;

The categoryId in the products table is the foreign key column that refers to the categoryId column in the categories table.

Because we don’t specify any ON UPDATE and ON DELETE clauses, the default action is RESTRICT for both update and delete operation.

INSERT INTO categories(categoryName)
VALUES ('Smartphone'), ('Smartwatch');

INSERT INTO products(productName, categoryId) VALUES('iPhone',1);

INSERT INTO products(productName, categoryId)
VALUES('iPad',3); //error

UPDATE categories SET categoryId = 100 WHERE categoryId = 1; //error

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT NOT NULL,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
    REFERENCES categories(categoryId)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=INNODB;

INSERT INTO products(productName, categoryId)
VALUES
    ('iPhone', 1), 
    ('Galaxy Note',1),
    ('Apple Watch',2),
    ('Samsung Galary Watch',2);

DELETE FROM categories WHERE categoryId = 2;

CREATE TABLE categories(
    categoryId INT AUTO_INCREMENT PRIMARY KEY,
    categoryName VARCHAR(100) NOT NULL
)ENGINE=INNODB;

CREATE TABLE products(
    productId INT AUTO_INCREMENT PRIMARY KEY,
    productName varchar(100) not null,
    categoryId INT,
    CONSTRAINT fk_category
    FOREIGN KEY (categoryId) 
        REFERENCES categories(categoryId)
        ON UPDATE SET NULL
        ON DELETE SET NULL 
)ENGINE=INNODB;

INSERT INTO categories(categoryName)
VALUES
    ('Smartphone'),
    ('Smartwatch');

INSERT INTO products(productName, categoryId)
VALUES
    ('iPhone', 1), 
    ('Galaxy Note',1),
    ('Apple Watch',2),
    ('Samsung Galary Watch',2);

UPDATE categories SET categoryId = 100 WHERE categoryId = 1;

DELETE FROM categories WHERE categoryId = 2;