Database Tutorial

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.