DDL, Data Types, Keys, and Constraints in MySQL

Introduction to Data Definition Language (DDL)

DDL stands for Data Definition Language, which is a set of commands to describe the overall database design in SQL (Structured Query Language). The main commands in DDL include:

• CREATE: Used to create new objects.
• ALTER: Used to modify the structure of existing objects.
• DROP: Used to delete objects.

---

Using CREATE, ALTER, and DROP Commands in MySQL

a. CREATE

The CREATE command is used to create a new database, new table, or new view.

• Example: CREATE DATABASE toko_buku;
• Explanation: This command creates a new database named toko_buku.

b. ALTER

The ALTER command is used to modify the structure of an existing table. This operation can be performed to add, delete, or modify columns, modify the table, or assign new attributes to columns.

• Example: ALTER TABLE buku ADD judul_buku varchar(20);
• Explanation: This command adds a new field (column) named judul_buku with data type varchar and maximum character length of 20 to the buku table.

c. DROP

The DROP command is used to permanently delete objects in the database. These objects can be the database itself, tables, functions, indexes, procedures, triggers, or views.

• Example: DROP DATABASE toko_buku;
• Explanation: This command deletes the database named toko_buku.

---

Data Types in MySQL

Choosing the correct data type is crucial for database storage efficiency. The following are categories of data types in MySQL:

A. Numeric Data Types

Used to store numeric data.

• TINYINT: Stores integers (positive/negative). Range: -128 to 127. Size: 1 byte (8 bits).
• SMALLINT: Stores integers. Range: -32768 to 32767. Size: 2 bytes (16 bits).
• MEDIUMINT: Stores integers. Range: -8388608 to 8388607. Size: 3 bytes (24 bits).
• INT: Stores integers. Range: -2147483648 to 2147483647. Size: 4 bytes (32 bits).
• BIGINT: Stores large integers. Range: -9223372036854775808 to 9223372036854775807. Size: 8 bytes (64 bits).
• FLOAT: Stores single-precision floating-point numbers. Size: 4 bytes (32 bits).
• DOUBLE / REAL: Stores double-precision floating-point numbers. Size: 8 bytes (64 bits).
• DECIMAL / NUMERIC: Stores fixed-precision numbers. Size: 8 bytes (64 bits).

B. String Data Types

Used to store character or text data.

• CHAR: Stores fixed-length strings (e.g., item IDs, ID card numbers). Range: 0 to 255 characters.
• VARCHAR: Stores variable-length strings. Range: 0 to 65,535 characters (in recent versions).
• TINYTEXT: Stores short text. Range: 0 to 255 characters.
• TEXT: Stores standard text. Range: 0 to 65,535 characters.
• MEDIUMTEXT: Stores medium-length text. Range: 0 to 16,777,215 characters.
• LONGTEXT: Stores very long text. Range: 0 to 4,294,967,295 characters.

C. Date and Time Data Types

Used to store date and time data.

• DATE: Stores dates (Format: YYYY-MM-DD). Range: 1000-01-01 to 9999-12-31. Size: 3 bytes.
• TIME: Stores time (Format: HH:MM:SS). Range: -838:59:59 to +838:59:59. Size: 3 bytes.
• DATETIME: Combination of date and time (Format: YYYY-MM-DD HH:MM:SS). Range: 1000-01-01 00:00:00 to 9999-12-31 23:59:59. Size: 8 bytes.
• YEAR: Stores years (Format: YYYY). Range: 1900 to 2155. Size: 1 byte.

D. BLOB Data Types (Binary)

BLOB stands for Binary Large Object. Used to store pure binary data, case-sensitive, and ideal for multimedia files such as images, documents, or audio.

• BIT: Stores binary data. Range: 64 binary digits.
• TINYBLOB: Maximum size of 255 bytes.
• BLOB: Maximum size of 65,535 bytes.
• MEDIUMBLOB: Maximum size of 16,777,215 bytes.
• LONGBLOB: Maximum size of 4,294,967,295 bytes.

---

Types of Keys in SQL

Keys in SQL are combinations of one or more attributes (columns) used to uniquely identify rows in a table or to link (relate) one table to another.

• Super Key: A set of attributes that can uniquely identify each row in a table.
• Candidate Key: A minimal super key with no redundant attributes. Candidate keys are chosen from the smallest combination of fields that can uniquely identify a record.
• Primary Key: The selected candidate key that serves as the main identifier for a record. Data in the primary key cannot be null (Not Null) and must be unique. Each table can have only one Primary Key.
• Alternate Key: Candidate keys that are not selected as the Primary Key.
• Foreign Key: A key used to relate one table to another, forming a parent-child relationship. The Primary Key in the parent table is referenced by the Foreign Key in the related child table.
• Composite Key: A key consisting of two or more attributes to uniquely identify an entity. Typically used when no single column qualifies as a Primary Key.

---

Types of Constraints in MySQL

Constraints are rules applied to columns or tables to maintain data integrity and validity.

• NOT NULL: Ensures that a column cannot be left empty when inserting a record. The column must contain data.
• UNIQUE: Ensures that all values in a column are distinct (no duplicate records).
• PRIMARY KEY: Combines the NOT NULL and UNIQUE constraints. Uniquely identifies each row in a table. Only one PRIMARY KEY is allowed per table.
• FOREIGN KEY: Maintains referential integrity between tables. Ensures that values in the child column have a valid reference in the parent column (another table).
• CHECK: Validates data before storing it in the database. The data is evaluated against a boolean condition; if true, it is stored; if false, SQL rejects it and displays an error message.