Sequences are primarily used to define unique, incremental values commonly used for an ID column or a table’s primary key, e.g., an employee ID.  Prior to Oracle 12C the standard method used to implement a sequence in the method I just described was by using a trigger.  A default table constraint implementing a sequence seems much more efficient and straight-forward than using a trigger.  Prior to Oracle 12C you could not create a default table constraint implementing a sequence, but now you can.

You can create a default constraint in the CREATE TABLE statement or by using ALTER TABLE.  I will show examples using both methods.

Adding the default constraint implementing the sequence with ALTER TABLE:

Adding the default constraint implementing the sequence in the CREATE TABLE statement:

Personal Preference
Whenever I create table constraints, by habit, I always create them using ALTER TABLE instead of in the CREATE TABLE statement.  This stems from the way I add constraints in SQL Server, i.e., being able to name them.  Unfortunately, Oracle doesn’t allow you to name a default constraint at this time.  In my opinion, creating table constraints in separate commands helps to make the script neater and easier to read.



Leave a Reply

Your email address will not be published. Required fields are marked *