Database Reference
In-Depth Information
▼
Input
INSERT INTO Customers
VALUES(NULL,
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA',
NULL,
NULL);
Note
No Output
INSERT
statements usually generate no output.
▼
Analysis
The preceding example inserts a new customer into the
customers
table. The
data to be stored in each table column is specified in the
VALUES
clause, and a
value must be provided for every column. If a column has no value (for exam-
ple, the
cust_contact
and
cust_email
columns), the
NULL
value should be
used (assuming the table allows no value to be specified for that column). The
columns must be populated in the order in which they appear in the table defi-
nition. The first column,
cust_id
, is also
NULL
. This is because that column
is automatically incremented by MariaDB each time a row is inserted. You'd
not want to specify a value (that is MariaDB's job), and nor could you omit the
column (as already stated, every column must be listed), and so a
NULL
value is
specified (it is ignored by MariaDB, which inserts the next available
cust_id
value in its place).
Although this syntax is indeed simple, it is not at all safe and should gener-
ally be avoided at all costs. The previous SQL statement is highly dependent
on the order in which the columns are defined in the table. It also depends
on information about that order being readily available. Even if it is available,
there is no guarantee that the columns will be in the exact same order the next
time the table is reconstructed. Therefore, writing SQL statements that depend
on specific column ordering is unsafe. If you do so, something will inevitably
break at some point.
The safer (and unfortunately more cumbersome) way to write the
INSERT
statement is as follows:
