MYSQL Constraints

 PK - Primary Key: Uniquely identifies each record in a table.

NN - Not Null: Ensures a column cannot have a NULL value.

UQ - Unique: Ensures all values in a column are different.

B - Binary: Stores binary byte strings.

UN - Unsigned: For numeric types, allows only non-negative values.

ZF - Zero Fill: Pads numeric values with zeros to the left.

AI - Auto Increment: Automatically generates a unique number for new records.

G - Generated Column: Value is computed from an expression.

PK - Primary Key

A primary key uniquely identifies each record in a table. It must contain unique values and cannot have NULL values. 

Example:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

Here, StudentID is the primary key.

NN - Not Null 

This constraint ensures that a column cannot have NULL values. 

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    Email VARCHAR(100)
);

The Name column cannot be left empty when inserting or updating records.

UQ - Unique 

This constraint ensures all values in a column are different. 

Example:

CREATE TABLE Users (
    UserID INT PRIMARY KEY,
    Username VARCHAR(50) UNIQUE,
    Password VARCHAR(50)
);

The Username column must have unique values for all records.

B - Binary 

This is used to store binary byte strings. 

Example:

CREATE TABLE Files (
    FileID INT PRIMARY KEY,
    FileName VARCHAR(100),
    FileContent BINARY(1000)
);

FileContent can store binary data like images or documents.

UN - Unsigned 

For numeric types, this allows only non-negative values. 

Example:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Quantity INT UNSIGNED
);

Quantity can only store values >= 0.

ZF - Zero Fill 

This pads numeric values with zeros to the left. 

Example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderNumber INT(6) ZEROFILL
);

If OrderNumber is 123, it will be displayed as 000123.

AI - Auto Increment 

This automatically generates a unique number for new records. Example:

CREATE TABLE Customers (
    CustomerID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(50),
    Email VARCHAR(100)
);

CustomerID will automatically increment for each new record.

G - Generated Column 

The value is computed from an expression. 
Example:

CREATE TABLE Rectangle (
    ID INT PRIMARY KEY,
    Width INT,
    Height INT,
    Area INT GENERATED ALWAYS AS (Width * Height)
);

The Area column is automatically calculated based on Width and Height.

Difference Between INT and INT(5) data type

Size of INT data type: An INT in MySQL is a 4-byte integer that can store values from -2,147,483,648 to 2,147,483,647 (for signed integers) or 0 to 4,294,967,295 (for unsigned integers).

Difference between INT and INT(5): The number in parentheses, like in INT(5), is often misunderstood. It does not affect the storage size or range of values that can be stored. Here's what it actually means:

1. Display width: INT(5) specifies a display width of 5 digits. This only affects how the data is displayed in some contexts, not how it's stored.
2. Padding: When used with ZEROFILL, it determines how many zeros to pad on the left side of the number when displaying it.
3. No constraint: It doesn't constrain the number of digits that can be stored. You can still store numbers larger than 5 digits in an INT(5) column.

Example:

CREATE TABLE example (

    a INT,

    b INT(5) ZEROFILL

);

INSERT INTO example VALUES (123, 123), (12345, 12345), (1234567, 1234567);

SELECT * FROM example;

a               b

123         00123

12345     12345

1234567 1234567

Key points:

• Both 'a' and 'b' can store the same range of values.
• 'b' will display with leading zeros if the number has fewer than 5 digits.
• Numbers larger than 5 digits are displayed normally in 'b'.

In modern MySQL usage, specifying a display width for integer data types is deprecated, and it's recommended to use INT without the parentheses unless you specifically need ZEROFILL functionality.