Tag: SQL

Posted on

OracleDB12c New Feature: Identity Column

Let’s talk about a “developer” feature today. If you have a need to generate a column value [mostly primary key] based on a sequence, in pre-12c Oracle database we have to create a number data type column in the table, and use a sequence that is separately created. There is no restriction that the sequence can be used only to populate one table or column.

To comply with ANSI SQL [extension] Oracle 12c now includes the “INDENTITY” column. While defining the table [CREATE TABLE] or modifying the table [ALTER TABLE] you can now define the IDENTITY column with a “sequence generator” definition.

According to Oracle documentation, the syntax for this new option in CREATE TABLE is

GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]

GENERATED keyword tells Oracle that this column value is generated.

ALWAYS is the default and specifies that the column value is never assigned and during INSERT/UDPATE statements, this column will always be evaluated to NULL – value will be populated by Oracle based on the “identity_options”.

BY DEFAULT specifies that the column value is generated by Oracle (similar to ALWAYS), but you can explicitly assign values to the column using INSERT/UPDATE statements. If you specify ON NULL with BY DEFAULT, generated value is assigned to the column only when the column value is evaluated to NULL during INSERT/UPDATE.

“identity_options” is basically the syntax for sequence generator – same as the CREATE SEQUENCE options.

There are some restrictions, please read the Oracle documentation link above. Notable one is that you can have only one IDENTITY column per table. IDENTITY column has a NOT NULL constraint automatically created.

 

 

Posted on

OracleDB12c New Feature: Cascaded Truncate

Oracle Database 12c, now TRUNCATE statement is enhanced with  CASCADE option. This is pretty useful, that you need not worry about truncating all “child” tables before truncating the parent. With the CASCADE option, Oracle will truncate all tables with a foreign key constraint to the table being truncated. The foreign key must be ENABLED and defined with “ON DELETE CASCADE” clause. There is no set limit to the recursive level of cascaded truncate. All child, grand child, great grandchild, etc tables will be truncated.

Oracle12c also extended the same feature to truncating a partition. Now, ALTER TABLE … TRUNCATE PARTITION can include the CASCADE option as well. Specify CASCADE to truncate the corresponding partition(s) or subpartition(s) in all reference-partitioned child tables of table being truncated.