How to Automatically Generate Primary Key Values Using Vertabelo’s Auto-Generation Features

Learn how to set up automatically-generating primary key values in SQL, and then see how to use Vertabelo to apply auto-generation to popular database management systems.

The primary key is a fundamental concept in relational databases. This article will discuss several ways to make an auto-generating primary key in SQL. We’ll also cover how to implement auto-generation in the Vertabelo data modeler.

Understanding Primary Keys

In relational databases,  a primary key constraint specifies that the value stored in that column must be unique across all records in the table. (Or, in the case of composite (multi-column) primary keys, that the combination of values is unique in that table). A table has one primary key, even if it’s composed of more than one attribute.

Initially, the primary key (and other integrity constraints) is defined through SQL (Structured Query Language) commands during or after table creation.

Using Vertabelo, let’s make a simple Customers table to demonstrate this point. This has five attributes: id, name, birthday, street, and city. The id field (an attribute that will have unique numerical values for each record) is the primary key. Therefore, this table will have a primary key of only one attribute. If you have doubts about why you need a primary key here, see our article Always Define a Primary Key.

If we choose to create the primary key using pure SQL, we could insert the key directly into the table's construction code or add it later with an ALTER command. Using Vertabelo to generate the SQL script, we get this code:

CREATE TABLE Customers (
    id int  NOT NULL,
    name varchar(100)  NOT NULL,
    birthday date  NOT NULL,
    street varchar(100)  NOT NULL,
    city int  NOT NULL,
    CONSTRAINT Customers_pk PRIMARY KEY (id)
);

The UNIQUE and NOT NULL clauses are implicit when defining a primary key; it’s not necessary to specify them alongside PRIMARY KEY because this constraint alone will ensure that no value is repeated (UNIQUE) and that no value is entered blank (NOT NULL).

In the above command, the CONSTRAINT clause creates the Customers_pk key when the table is created. Another way to do it is to use the ALTER TABLE statement:

CREATE TABLE Customers (
    id int  NOT NULL,
    name varchar(100)  NOT NULL,
    birthday date  NOT NULL,
    street varchar(100)  NOT NULL,
    city int  NOT NULL
);

ALTER TABLE Customers ADD PRIMARY KEY Customers_pk (id)

Above, the table is created and then modified through the ALTER command.

Both groups of commands have the same objective: create a table with a primary key. However, in this case, you’ll have to control the primary key values programmatically through your application or middleware. You can also create it using other (more productive) database resources.

How to Automatically Generate Primary Key Values in a Database

1. Sequences

Sequences are structures created within the database; they are objects that return a different value with each access. Sequences are used by Oracle, PostgreSQL, SQL Server, and other database management systems (DBMSs). Their only function is to control the primary key’s unique values, ensuring that the primary key will not receive any repeated values (which could cause data insertion errors).

A sequence can be created using the following command:

CREATE SEQUENCE customers_id_seq
START 1
INCREMENT 1
NOMINVALUE
NOMAXVALUE
OWNED BY customers.id;

This command creates a simple sequence using some basic parameters. The object created will start the count at 1, incrementing by one each time it’s accessed. There’s no minimum or maximum value, so it can increase freely.

This sequence is owned by the Customers table; when this command is executed, the new object customers_id_seq is created. Every time an INSERT clause runs on this table, a unique sequential number will be generated and put in the id field. 

2. Triggers

A trigger is a resource linked to the invocation of some event, i.e. UPDATE, SELECT, or INSERT. For each trigger, we configure the event as well as when it will be executed (e.g. before or after the event). As the events are run in tables, we can only perform UPDATE, SELECT, and INSERT on them; the trigger will also be linked to a table.

A trigger’s primary function is to perform a code snippet (which is programmed within it) at a particular time and event. Except for the PostgreSQL database, these code snippets are usually written in basic SQL. Therefore, you can use triggers to generate new values for a primary key, selecting the maximum value of that field and adding one before the insertion is performed. For example, a trigger that can generate these values can be written as follows:

CREATE TRIGGER customers_new_id 
BEFORE INSERT ON customers
FOR EACH ROW 
SET NEW.id = (SELECT MAX(id) + 1 FROM customers);

Before the INSERT command is executed in these triggers, the id is replaced for the maximum customers table id, adding one. This guarantees that it will insert a new value at the primary key.

The use of triggers for generating new values for a primary key was a common practice. It’s fallen into some disuse after the introduction of SEQUENCES; there was often one trigger per table, which generated a large volume of extra SQL code. Furthermore, sequences are much safer and less prone to failure; triggers will eventually slow the performance or cause all sorts of concurrency problems, depending on your use of transactions and locking.

3. Functions

Functions are stored preprocessed code that a database user can call to perform a task. A database can have internal functions or UDFs (user-defined functions), the latter being responsible for generating a new identifier for a primary key. They allow executing more complex blocks of code than triggers, i.e. when the data needs some treatment before insertion.

Combining the concepts presented in this article, SEQUENCES themselves have functions to modify their numbering (the NEXTVAL function) or retrieve the current SEQUENCE value (CURRVAL), if the user needs to do it manually.

SELECT nextval(‘customers_id_seq’);

This command calls a function (nextval) that returns the next number in the sequence. Or you can create a function specifically to generate new numbers using SELECT MAX(id) and an insert – almost in the same way as is done by a trigger.

4. Identity Columns

An identity column is one whose values automatically increment. Usually, it is common for databases to use identity columns, sequences, or both to guarantee unique primary key values.

Databases like PostgreSQL have the SERIAL type, a SEQUENCE disguised as an auto-increment field. MySQL, SQL Server, and Oracle have identity functions that allow the field to become auto-incremental. The following code demonstrates how to create an identity column in SQL Server:

CREATE TABLE Customers (
    id int  IDENTITY(1,1) PRIMARY KEY,
    name varchar(100)  NOT NULL,
    birthday date  NOT NULL,
    street varchar(100)  NOT NULL,
    city int  NOT NULL
);

The code above creates the same Customers table presented above, only with an auto-incrementing identity field (which starts at one and increases by one sequentially).

Designing Auto-Generation Features in Vertabelo

Vertabelo allows us to auto-generate primary key values using various resources. There are different types of databases and other ways to create primary keys available; the database modeler adapts according to the resource and the database to be used.

• MySQL:

  This database uses the auto-increment field, so Vertabelo provides a straightforward way of creating a primary key. First, go to Table Properties in the right panel.

  

  Select the id column: you’ll get access to the properties specific to that column.

  

  In Auto Increment, set the Autoincrement value to "Yes". This field will automatically become an identity column.

  

  If you take a look at the SQL generated by Vertabelo after these adjustments, you’ll see this code:

  CREATE TABLE Customers (
     id int NOT NULL AUTO_INCREMENT,
     name varchar(100) NOT NULL,
     birthday date NOT NULL,
     street varchar(100) NOT NULL,
     city varchar(50) NOT NULL,
     CONSTRAINT Customers_pk PRIMARY KEY (id)
  );
  

  For MySQL, the Vertabelo tool already allows that all its fields are placed as "Auto Increment" during the modeling itself, thus creating a primary key that already guarantees unique values.

• Oracle/SQL Server/PostgreSQL/IBM DB2

  These databases can create SEQUENCES. These objects, as explained earlier, will contain the numbering that will be used as keys and prevent the primary key from having repeated values.

  To create a SEQUENCE in Vertabelo, click on Model Structure and then on Sequences.

  

  In this tab, a new option will open. Click on the three side dots and select Add Sequence to insert a new object.

  

  On the right side, you’ll see a box with all the options for that sequence: name, comment, initial value, increment value, and maximum or minimum value. In this example, the maximum value was left blank (as we don't want limitation), and the rest were set to 1.

  

  Using SQL Preview, you can see the result of the object you configured: Vertabelo will generate the code for the sequence that has precisely the parameters you passed. This feature increases productivity and saves you from manually developing these objects once your model is ready. Below is the SQL generated for this sequence:

  CREATE SEQUENCE customers_id_seq
       INCREMENT BY 1
       MINVALUE 1
       NOMAXVALUE
       START WITH 1
       NOCACHE
       NOCYCLE;
  

  Databases like MySQL and PostgreSQL have the SERIAL type. This data type works as an integer automatically associated with a SEQUENCE created by the DBMS . Vertabelo also supports this type of data, making modeling even easier.

  

  Using the Additional SQL Scripts feature, you can create triggers for unique values or even manipulate SEQUENCES, as shown in the figure below:

  

  To do this, just go to the tab in the right corner (below Table properties) and insert the additional script you want to be created. The code will be as shown below:

  CREATE OR REPLACE TRIGGER customer_id
  BEFORE INSERT ON customers
  FOR EACH ROW
  BEGIN
   SELECT customers_id_seq.NEXTVAL
   INTO   :new.id
   FROM   dual;
  END;
  -- tables
  -- Table: Customers
  CREATE OR REPLACE TRIGGER customers_insert
  BEFORE INSERT ON customers
  FOR EACH ROW
  
  BEGIN
   SELECT customers_id_seq.NEXTVAL
   INTO   :new.id
   FROM   dual;
  END;
  
  CREATE TABLE Customers (
     id integer  NOT NULL,
     name varchar2(100)  NOT NULL,
     birthday date  NOT NULL,
     street varchar2(100)  NOT NULL,
     city varchar2(50)  NOT NULL,
     CONSTRAINT Customers_pk PRIMARY KEY (id)
  ) ;
  

As you can see in this article, Vertabelo covers all possible ways to generate unique values in SQL: using triggers, sequences, functions, identity columns, and serial types. This feature allows you to model for multiple databases simultaneously, ensuring model productivity and quality. It also helps porting systems between different databases while maintaining compatibility.