Database Reference
In-Depth Information
Note
Overriding
AUTO_INCREMENT
Need to use a specific value if a column is designated
as
AUTO_INCREMENT
? You can—simply specify a value in the
INSERT
statement,
and as long as it is unique (has not been used yet) that value will be used instead of an
automatically generated one. Subsequent incrementing will start using the value manu-
ally inserted. (See the table population scripts in Appendix B, “The Example Tables” for
examples of this.)
Tip
Determining the
AUTO_INCREMENT
Value One downside of having MariaDB
generate (via auto increment) primary keys for you is that you don't know what those
values are.
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.
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
key-
word 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;
