Database Tutorial

Course Tutorial Site

Site Admin

Insertion Signatures

without comments

The INSERT statement works on the premise of a default or overriding signature, just like an ordinary function or method call. That’s true because an INSERT statement is a method call that inserts one or more rows into a table. Unless you override the default order of the columns, an INSERT statement assumes the default signature.

The default signature is the ordered set of columns written to the database catalog when you created the table. Alternatively, the default signature is the ordered set of columns after an ALTER statement that adds, modifies, or drops a column from the table. In some implementation, like MySQL, an ALTER statement can re-order the sequencing of columns. when you created or last modified the table. The default signature is defined as the positional order by data type of the columns found in the database catalog for a table, view, or object type.

The database engine parses an INSERT statement at runtime to see if it matches the default signature, unless you provide an override signature. The examples demonstrate inserting both mandatory and optional columns. Mandatory columns have a 1..1 cardinality and are typically NOT NULL constrained. Optional columns have a 0..1 cardinality and are typically nullable or unconstrained.

The following CREATE statement defines the table for these INSERT statement examples. It has two nullable columns.

1
2
3
4
5
6
7
CREATE TABLE signature
( signature_id    NUMBER       CONSTRAINT pk1_signature PRIMARY KEY
, first_name      VARCHAR2(20) CONSTRAINT nn1_signature NOT NULL
, middle_initial  VARCHAR2(1)
, last_name       VARCHAR2(30) CONSTRAINT nn2_signature NOT NULL
, signed          DATE         CONSTRAINT nn3_signature NOT NULL
, signature       BLOB);

The examples show you how to insert values for the following:

  1. An explicit signature, which includes positionally matched values for both mandatory and optional columns.
  2. An implicit signature, which includes positionally matched values for mandatory columns and null values for optional columns.
  3. An override signature, which includes named notations that may shift the order of columns and allows you to only insert mandatory column values.

Rather than repeat everything in each section, the various signature discussion build on each other. You should read them from start to end.

Explicit Default Signature

The example uses a default signature. Default signatures require that you provide a value for all columns of the table whether they’re mandatory or optional. The list of values must match the data type of the column. When the data types match, they must also comply with any physical size limitation, like a variable length string no greater than 30.

1
2
3
4
5
6
7
8
9
INSERT INTO signature
VALUES
( 1             -- A number that fits within a maximum number (38 digits).
,'David'        -- A string that fits within a 20 character width.
,'O'            -- A string that fits within a 1 character width.
,'McKay'        -- A string that fits within a 40 character width.
,'08-JUN-1968'  -- A date, by leveraging the default date format.
, empty_blob()  -- An empty binary large object.
);

The last statement includes a comment on the last line, so the closing parenthesis is moved to the line below. Another alternative in an Oracle database would be to put the closing parenthesis empty_blob() function call and a forward slash on the last line. The empty_blob() allows you to initialize the column in that row to hold a binary large object (BLOB) for the signature image file. Relational databases, like MySQL, let you simply enter a NULL keyword instead.

Implicit Default Signature

The example uses a default signature. As mentioned, default signatures require that you provide a value for all columns of the table whether they’re mandatory or optional, and you must match the values in the list to data type of the column.

You have the option of passing a null value to an optional column. One way to do so, is to pass a set of single quotes (or apostrophes) without anything between them. Another way to pass a null value is to use the NULL keyword, which may be in lower, upper, or mixed case in most database management system implementations. Both of these approaches are shown in the following example.

1
2
3
4
5
6
7
8
INSERT INTO signature
VALUES
( 2             -- A number that fits within a maximum number (38 digits).
,'Brigham'      -- A string that fits within a 20 character width.
,''             -- A NULL string.
,'Young'        -- A string that fits within a 30 character width.
,'08-JUN-1868'  -- A date, by leveraging the default date format.
, NULL);

You can see the two apostrophes without any intervening white space insert a null value string for the third positional value. Likewise, the last item uses a NULL keyword to insert a null value.

Override Signature

The example uses an override signature. You need only list the mandatory columns in a comma-delimited list, as shown below. Then, the VALUES clause must provide values that mirror the column data types that match your overriding signature.

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO signature
( signature_id
, last_name     -- A string that fits within a 30 character width.
, first_name    -- A string that fits within a 20 character width.
, signed )
VALUES
( 3             -- A number that fits within a maximum number (38 digits).
,'Snow'         -- A string that fits within a 30 character width.
,'Lorenzo'      -- A string that fits within a 20 character width.
, TO_DATE('Jun 6, 1891','MON DD, YYYY')  -- An override date format.
);

You may also include optional column values in an override signature list. You typically do that when you want to ensure which values are passed to optional columns.

The parser works in one or two steps. First, it checks to see if a column contains a value or references another column that would contain value of the same data type as the target column. If the column is not null constrained, the statement second parsing action checks for a value. If either of these checks fails, an error is raised, and the statement fails. The specific code and messages differ between various database management systems.

Written by michaelmclaughlin

October 18th, 2009 at 11:24 am

Posted in

Leave a Reply