• Choose a category
• All
• Classic-Fiction

Consider this scenario: You are adding a new order. This requires creating a single row in the orders table and then a row for each item ordered in the orderitems table. The order_num is stored along with the order details in orderitems. This is how the orders and orderitems table are related to each other. And that obviously requires that you know the generated order_num after the orders row was inserted and before the orderitems rows are inserted.

So how could you obtain this value when an AUTO_INCREMENT column is used? By using the last_insert_id() function, like this:

SELECT last_insert_id();

This returns the last AUTO_INCREMENT value, which you can then use in subsequent SQL statements.

* * *

Specifying Default Values

MariaDB enables you to specify default values to be used if no value is specified when a row is inserted. Default values are specified using the DEFAULT keyword in the column definitions in the CREATE TABLE statement.

Look at the following example:

Input

CREATE TABLE orderitems

(

order_num int NOT NULL ,

order_item int NOT NULL ,

prod_id char(10) NOT NULL ,

quantity int NOT NULL DEFAULT 1,

item_price decimal(8,2) NOT NULL ,

PRIMARY KEY (order_num, order_item)

) ENGINE=Aria;

Analysis

This statement creates the orderitems table that contains the individual items that make up an order. (The order itself is stored in the orders table.) The quantity column contains the quantity for each item in an order. In this example, adding the text DEFAULT 1 to the column description instructs MariaDB to use a quantity of 1 if no quantity is specified.

* * *

Caution: Functions Are Not Allowed

Unlike most DBMSs, MariaDB (like MySQL) does not allow the use of functions as DEFAULT values; only constants are supported.

* * *

* * *

Tip: Using DEFAULT Instead of NULL Values

Many database developers use DEFAULT values instead of NULL columns, especially in columns that will be used in calculations or data groupings.

* * *

Engine Types

You may have noticed that the CREATE TABLE statements used thus far all ended with a ENGINE=Aria statement.

Like every other DBMS, MariaDB has an internal engine that actually manages and manipulates data. When you CREATE TABLE that engine is used to actually create the tables, and when you SELECT or perform any other database processing, the engine is used internally to process your request. And for the most part, the engine is buried within the DBMS and you need not pay much attention to it.

But unlike most other DBMSs, MariaDB does not come with a single engine. Rather, it ships with several different engines (the ones that come with MySQL as well as additional ones), all buried within the server, and all capable of executing commands like CREATE TABLE and SELECT.

So why bother shipping multiple engines? Because each has different capabilities and features, and being able to pick the right engine for a job gives you unprecedented power and flexibility.

Of course, you are free to totally ignore database engines. If you omit the ENGINE= statement, the default engine will be used, and most of your SQL statements will work as is. But not all, and that is why this is important (and why two different engines are used in the sample tables used in this book).

Here are several engines to be aware of:

• InnoDB is a transaction-safe engine (see Chapter 26, “Managing Transaction Processing”). It does not support full-text searching.

• MEMORY is functionally equivalent to MyISAM, but as data is stored in memory (instead of on disk) it is extremely fast (and ideally suited for temporary tables).

• MyISAM is a high-performance engine. It supports full-text searching (see Chapter 18, “Full-Text Searching”), but does not support transactional processing.

• ARIA (specified as ENGINE=Aria) is a new transaction-safe engine that also supports full-text searching and vital crash recovery features.

Engine types may be mixed, so within a single database you can have different tables using different