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 used 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 the CREATE, ALTER, and DROP Commands in MySQL

a. CREATE

The CREATE command is used to create new databases, new tables, or new views.

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

b. ALTER

The ALTER command is used to change the structure of a table that has already been created. This operation can add, delete, modify columns, alter 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 the varchar data type and a maximum length of 20 characters to the buku table.

c. DROP

The DROP command permanently removes an object from the database. The object can be the database itself, a table, a function, an index, a procedure, a trigger, or a view.

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

Data Types in MySQL

Choosing the right data type is very important for database storage efficiency. Below are the categories of data types in MySQL:

A. Numeric Data Types

Used to store numeric data.

TINYINT: Stores whole numbers (positive/negative). Range: -128 to 127. Size: 1 byte (8 bits).
SMALLINT: Stores whole numbers. Range: -32768 to 32767. Size: 2 bytes (16 bits).
MEDIUMINT: Stores whole numbers. Range: -8388608 to 8388607. Size: 3 bytes (24 bits).
INT: Stores whole numbers. Range: -2147483648 to 2147483647. Size: 4 bytes (32 bits).
BIGINT: Stores large-scale whole numbers. 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-point numbers with very high precision. Size: 8 bytes (64 bits).

B. String Data Types

Used to store character or text data.

CHAR: Stores fixed-length string data (e.g., item ID, national ID number). Range: 0 to 255 characters.
VARCHAR: Stores variable-length string data. Range: 0 to 65,535 characters (in the latest versions).
TINYTEXT: Stores short text data. Range: 0 to 255 characters.
TEXT: Stores standard text data. Range: 0 to 65,535 characters.
MEDIUMTEXT: Stores medium-length text data. Range: 0 to 16,777,215 characters.
LONGTEXT: Stores very long text data. Range: 0 to 4,294,967,295 characters.

C. Date and Time Data Types

Used to store date and time data.

DATE: Stores a date (Format: YYYY-MM-DD). Range: ‘1000-01-01’ to ‘9999-12-31’. Size: 3 bytes.
TIME: Stores a 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 a year (Format: YYYY). Range: 1900 to 2155. Size: 1 byte.

D. BLOB Data Types (Binary)

BLOB stands for Binary Large Object. It is used to store pure binary data, is case-sensitive, and is 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

A key in SQL is a combination of one or more attributes (columns) that serves to uniquely distinguish rows of data in a table or to connect (relate) one table with another.

Super Key: A set of attributes that can be used to uniquely identify each row of data in a table.
Candidate Key: A minimal super key that has no redundant attributes. A candidate key is chosen from the smallest combination of fields capable of identifying unique values.
Primary Key: The candidate key selected as the main identifier of a record. Data in a primary key must not be empty (Not Null) and must not duplicate. Each table can have only one Primary Key.
Alternate Key: A candidate key that is not chosen as the Primary Key.
Foreign Key: A key used to relate one table to another, forming a parent and 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. Usually used when no single column satisfies the requirements for 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 be filled with data.
UNIQUE: Ensures that every value in a column is different from the others (no duplicate records).
PRIMARY KEY: A combination of 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 the value in the child column has a valid reference in the parent column (in another table).
CHECK: Performs validation or checking before data is saved to the database. Data is evaluated with a boolean value; if it meets the condition it is considered true and is stored, but if false, SQL rejects it and displays an error message.