• While data types provide basic constraints, additional constraints are needed to ensure data validity and business logic correctness
• Table constraints are essential to guarantee that future data inserted into database tables meets expectations
• Constraints essentially force programmers to insert correct data through technical means
• From MySQL's perspective, all inserted data should comply with defined constraints
• The ultimate goal of constraints is to ensure data integrity and predictability

1. NULL Constraints

• Two possible values: NULL(default) and NOT NULL(must have value)
• Database fields are typically nullable by default, but in practice, fields should be non-nullable whenever possible
• NULL values cannot participate in calculations

SELECT NULL;
+------+
| NULL |
+------+
| NULL |
+------+

SELECT 1+NULL;
+--------+
| 1+NULL |
+--------+
|  NULL  |
+--------+

• Example: Creating a classroom table with class name and room
• From a business logic perspective:
• A class without a name is meaningless
• A room without a name makes it impossible to know where classes take place
• Therefore, database design should include these restrictions to prevent invalid data insertion

CREATE TABLE classroom(
    class_name VARCHAR(20) NOT NULL,
    room_location VARCHAR(10) NOT NULL
);

DESCRIBE classroom;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| class_name     | varchar(20) | NO   |     | NULL    |       |
| room_location  | varchar(10) | NO   |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+

-- Inserting without room data fails:
INSERT INTO classroom(class_name) VALUES('Math101');
ERROR 1364 (HY000): Field 'room_location' doesn't have a default value

2. Default Values

What are they?

• Default values: When a particular value frequently appears, it can be specified in advance
• DEFAULT: If set, user-provided data is used when available; otherwise, the default is used

CREATE TABLE users (
    username VARCHAR(20) NOT NULL,
    age TINYINT UNSIGNED DEFAULT 0,
    gender CHAR(2) DEFAULT 'M'
);

DESCRIBE users;
+----------+---------------------+------+-----+---------+-------+
| Field    | Type                | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| username | varchar(20)         | NO   |     | NULL    |       |
| age      | tinyint(3) unsigned | YES  |     | 0       |       |
| gender   | char(2)             | YES  |     | M       |       |
+----------+---------------------+------+-----+---------+-------+

INSERT INTO users(username) VALUES('alice');
SELECT * FROM users;
+----------+------+--------+
| username | age  | gender |
+----------+------+--------+
| alice    | 0    | M      |
+----------+------+--------+

-- Note: Only columns with DEFAULT can be omitted during insertion

What happens when NOT NULL and DEFAULT are combined?

• DEFAULT and NOT NULL complement eachother
• When a user omits a column during insertion, the default value is used
• Without a DEFAULT specified, insertion without a value results in an error
• These keywords serve different purposes:
• NOT NULL: Checks if the value is NULL during insertion
• DEFAULT: Provides a fallback value when the column is omitted
• Note: NOT NULL and DEFAULT are usually not needed together since DEFAULT inherently prevents NULL values

3. Column Comments

• Column comments: COMMENT provides descriptive text about fields, preserved in table creation statements for developer/DBA reference

CREATE TABLE employee (
    full_name VARCHAR(50) NOT NULL COMMENT 'Employee full name',
    birth_year YEAR DEFAULT 1990 COMMENT 'Year of birth',
    department CHAR(3) DEFAULT 'ENG' COMMENT 'Department code'
);

• Comments are not visible in DESCRIBE output:

DESCRIBE employee;
+-------------+---------------------+------+-----+---------+-------+
| Field       | Type                | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+-------+
| full_name   | varchar(50)         | NO   |     | NULL    |       |
| birth_year  | year                | YES  |     | 1990    |       |
| department  | char(3)             | YES  |     | ENG     |       |
+-------------+---------------------+------+-----+---------+-------+

• Comments are visible in SHOW CREATE TABLE:

SHOW CREATE TABLE employee\G
*************************** 1. row ***************************
Table: employee
Create Table: CREATE TABLE `employee` (
  `full_name` varchar(50) NOT NULL COMMENT 'Employee full name',
  `birth_year` year DEFAULT '1990' COMMENT 'Year of birth',
  `department` char(3) DEFAULT 'ENG' COMMENT 'Department code'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

4. Zerofill Attribute

• When learning databases, the significance of length specifications for numeric types can be confusing
• Looking at a table's creation statement:

SHOW CREATE TABLE numbers\G
*************************** 1. row ***************************
Table: numbers
Create Table: CREATE TABLE `numbers` (
  `value1` int(10) unsigned DEFAULT NULL,
  `value2` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

• What does int(10) mean? Aren't integers 4 bytes? What does 10 represent?
• Without the zerofill attribute, the number in parentheses has no meaning
• Basic insertion and retrieval:

INSERT INTO numbers VALUES(7, 42);
SELECT * FROM numbers;
+--------+--------+
| value1 | value2 |
+--------+--------+
| 7      | 42     |
+--------+--------+

• Adding the zerofill attribute changes display behavior:

ALTER TABLE numbers MODIFY value1 INT(5) UNSIGNED ZEROFILL;

SHOW CREATE TABLE numbers\G
*************************** 1. row ***************************
Table: numbers
Create Table: CREATE TABLE `numbers` (
  `value1` int(5) unsigned zerofill DEFAULT NULL,
  `value2` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

• Querying after adding zerofill:

SELECT * FROM numbers;
+-------+--------+
| value1| value2 |
+-------+--------+
| 00007 | 42     |
+-------+--------+

• The zerofill attribute automatically pads with zeros when the actual width is less than the specified width
• Important: This only affects display; the actual stored value remains 7**
• This demonstrates that zerofill is merely a formatting option for output, not affecting internal storage