Introduction to Oracle identity column #oracleplsql #banglatutorial #oracle

Summary: in this tutorial, you will learn how to use the Oracle identity column to easily define an automatic generated numeric column for a table. Introduction to Oracle identity column Oracle 12c introduced a new way that allows you to define an identity column for a table, which is similar to the AUTO_INCREMENT column in MySQL or IDENTITY column in SQL Server. The identity column is very useful for the surrogate primary key column. When you insert a new row into the identity column, Oracle auto-generates and insert a sequential value into the column. To define an identity column, you use the identity clause as shown below: GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ] Code language: SQL (Structured Query Language) (sql) First, the GENERATED keyword is mandatory. Second, you can specify an option to generate identity values: GENERATED ALWAYS: Oracle always generates a value for the identity column. Attempt to insert a value into the identity column will cause an error. GENERATED BY DEFAULT: Oracle generates a value for the identity column if you provide no value. If you provide a value, Oracle will insert that value into the identity column. For this option, Oracle will issue an error if you insert a NULL value into the identity column. GENERATED BY DEFAULT ON NULL: Oracle generates a value for the identity column if you provide a NULL value or no value at all. Third, you can have a number of options for the identity column. START WITH initial_value controls the initial value to use for the identity column. The default initial value is 1. INCREMENT BY internval_value defines the interval between generated values. By default, the interval value is 1. CACHE defines a number of values that Oracle should generate beforehand to improve the performance. You use this option for the column that has a high number of inserts.