primary_key:

“primary_key:” Overview

Sqac annotated go structs are used to supply the sqac runtime with the information required to create and alter tables in the database. The following examples illustrate the use of the ‘sqac:’ tags described in the Sqac Tag Overview section of this document.

“primary_key:” example

The following example illustrates the use of ‘sqac:’ tag “primary_key:” to declare a non-auto-incrementing table primary-key. Sqac expects table primary-keys to be declared as ints. In such a declaration, the caller must be careful to provide an unused value for the primary-key column.

  // Declare a struct to be used as the source for table creation.
  type Depot struct {
    DepotNum   int       `db:"depot_num" sqac:"primary_key:"`
    ...
    ...
  }

“primary_key:inc” example

The following example illustrates the use of ‘sqac:’ tag “primary_key:inc” to declare a table primary-key that auto-increments from 0. Most databases only support the use of an auto-incrementing sequence on a single column within a table declaration. Sqac expects columns setup with the auto-incrementing “primary_key:” to be declared as ints.

  // Declare a struct to be used as the source for table creation.
  type Depot struct {
    DepotNum   int       `db:"depot_num" sqac:"primary_key:inc"`
    ...
    ...
  }

“primary_key:inc;start:<int_value>” example

The following example illustrates the use of ‘sqac:’ tag “primary_key:” to declare a table primary-key that auto-increments from a starting value of 1000000. Sqac expects table primary-keys to be declared as ints.

  // Declare a struct to be used as the source for table creation.
  type Depot struct {
    DepotNum   int       `db:"depot_num" sqac:"primary_key:inc;start:1000000"`
    ...
    ...
  }

Compound “primary_key:” example

In cases where a single-key is not sufficient to differentiate between records, the “primary_key:” tag may be applied to multiple columns in a table declaration. So doing will result in the creation of a database constraint or compound primary-key containing the chosen columns depending on the database being targeted.

A similar effect can be obtained via declaration of a unique index containing the desired columns, or the use of the “constraint:” ‘sqac:’ tag. When declaring a compound key/constraint the data-types may be of any type with the exception of the auto-incrementing column (if any).

The following example illustrates the creation of a two-field compound key:

  type Depot struct {
    DepotNum   int       `db:"depot_num" sqac:"primary_key:"`
    DepotBay   int       `db:"depoy_bay" sqac:"primary_key:"`
    ...
    ...

As declared, the two-field compound key creates the following in each of the supported DB systems:

DBMS Result
Sqlite3 CREATE TABLE IF NOT EXISTS depot ( depot_num integer PRIMARY KEY AUTOINCREMENT, depot_bay integer, … … UNIQUE (depot_num, depot_bay) );
MySQL CREATE TABLE ‘depot’ ( ‘depot_num’ int(11) NOT NULL AUTO_INCREMENT, ‘depot_bay’ int(11) NOT NULL,**…** **…** PRIMARY KEY (‘depot_num’, ‘depot_bay’) ) ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = latin1
MSSQL CREATE TABLE [dbo].[depot]([depot_num] [int] IDENTITY(1,1) NOT NULL, [depot_bay] [int] NOT NULL, **…** **…** ) ON [PRIMARY] GO ALTER TABLE [dbo].[depot] ADD PRIMARY KEY CLUSTERED ( [depot_num] ASC, [depot_bay] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] **…** **…**
PostgreSQL CREATE TABLE public.depot(depot_num integer NOT NULL DEFAULT nextval(‘depot_depot_num_seq’::regclass),depot_bay integer NOT NULL,**…** **…** CONSTRAINT depot_pkey PRIMARY KEY (depot_num, depot_bay)) **…** **…** ;
SAP Hana