C – Insertion Signatures
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:
- An explicit signature, which includes positionally matched values for both mandatory and optional columns.
- An implicit signature, which includes positionally matched values for mandatory columns and null values for optional columns.
- 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.