MariaDB Data Definition for Creating Table

Posted on In

MariaDB Data Definition for setting up a table’s schema.

Table Example

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE customer(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    active_days INT,
    city VARCHAR(50),
    verified TINYINT(1) NOT NULL,
    created_at DATETIME
);

CREATE TABLE post(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  content TEXT NOT NULL,
  author_id INT UNSIGNED NOT NULL,
  CONSTRAINT `fk_post_author`
    FOREIGN KEY (author_id) REFERENCES customer (id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
  
);

Test Data

1
2
3
4
5
6
7
INSERT INTO customer (first_name, last_name, email, active_days, city, verified, created_at) VALUES('Alice', 'Ali', 'alice@example.com', 55, 'Boston', 1, "2012-04-19 13:08:22");
INSERT INTO customer (first_name, last_name, email, active_days, city, verified, created_at) VALUES('Dave', 'Dfe', 'dave@example.com', 20, 'New York', 0, "2019-04-19 10:30:19");
INSERT INTO customer (first_name, last_name, email, active_days, city, verified, created_at) VALUES('Bop', 'Bse', 'bop@example.com', 100, 'Boston', 0, now());
INSERT INTO customer (first_name, last_name, email, active_days, city, verified, created_at) VALUES('Cindy', 'Cge', 'cindy@example.com', 1, 'Las Vegas', 0, now());

INSERT INTO post (content, author_id) VALUES("Hello World...", 1);
INSERT INTO post (content, author_id) VALUES("Happy Programming...", 2);

Data Types

MariaDB supports Numeric, String and Date Times.
All DataTypes can be found from this documentation MariaDB Data Types

Column Definition

NOT NULL

Use the NULL or NOT NULL options to specify that values in the column may or may not be NULL, respectively.

AUTO_INCREMENT

Use AUTO_INCREMENT to create a column whose value can can be set automatically from a simple counter.

UNIQUE KEY

Use UNIQUE KEY (or just UNIQUE) to specify that all values in the column must be distinct from each other.

PRIMARY KEY

Use PRIMARY KEY (or just KEY) to make a column a primary key.

FOREIGN KEY

A foreign key is a constraint which can be used to enforce data integrity. It is composed by a column (or a set of columns) in a table called the child table, which references to a column (or a set of columns) in a table called the parent table.

Syntax:

1
2
3
4
5
6
7
8
[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Reference