This page focuses on common constraints and data types used when defining tables in MariaDB SQL, ensuring data integrity.
16. PRIMARY KEY - Uniquely Identify Each Row
Purpose: A PRIMARY KEY uniquely identifies each record in a table. It must contain unique values, and it cannot contain NULL values. Each table can have only one primary key.
Syntax (Column-level): column_name datatype PRIMARY KEY
Syntax (Table-level): CONSTRAINT pk_name PRIMARY KEY (column1, column2, ...)
Examples:
CREATE TABLE Students (
student_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE Enrollments (
enrollment_id INT,
student_id INT,
course_id INT,
PRIMARY KEY (enrollment_id),
FOREIGN KEY (student_id) REFERENCES Students(student_id)
);
17. FOREIGN KEY - Link Tables
Purpose: A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. It creates a link between the two tables, enforcing referential integrity.
Syntax (Column-level): column_name datatype REFERENCES parent_table(parent_column)
Syntax (Table-level): CONSTRAINT fk_name FOREIGN KEY (column1, column2, ...) REFERENCES parent_table(parent_column1, parent_column2, ...)
Examples:
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);
18. UNIQUE - Ensure Unique Values
Purpose: The UNIQUE constraint ensures that all values in a column (or a group of columns) are different. Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints, and it can allow one NULL value.
Syntax (Column-level): column_name datatype UNIQUE
Syntax (Table-level): CONSTRAINT uq_name UNIQUE (column1, column2, ...)
Examples:
CREATE TABLE Users (
user_id INT PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE
);
19. NOT NULL - Prevent NULL Values
Purpose: The NOT NULL constraint ensures that a column cannot have a NULL value. This means a value must be provided for that column when a new record is inserted or an existing record is updated.
Syntax: column_name datatype NOT NULL
Examples:
CREATE TABLE Products (
product_id INT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
20. DEFAULT - Set Default Value
Purpose: The DEFAULT constraint is used to provide a default value for a column. If no value is specified for that column during an INSERT statement, the default value will be assigned automatically.
Syntax: column_name datatype DEFAULT default_value
Examples:
CREATE TABLE Products (
product_id INT PRIMARY KEY,
product_name VARCHAR(255) NOT NULL,
price DECIMAL(10, 2) DEFAULT 0.00,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);