Skip to content

AUTO INCREMENT Self-increasing constraint

Auto-Increment Constraint is a feature used by MatrixOne Intelligence to automatically generate unique identifier values ​​for columns in a table. It allows you to automatically generate an incremental unique value for the specified autoincrement column when inserting a new row. This is very useful in many cases, such as being used as a primary key or an identifier.

Self-increase constraint characteristics

Self-increasing constraints can simplify the generation and management of identifiers. When using self-increment columns, you need to pay attention to the following points:

  • Self-incremented columns are usually used as primary keys, so their uniqueness should be guaranteed.
  • The data type of the self-increment column should be selected as appropriate integer type according to the requirements.
  • The value of the self-increment column is automatically generated when a new row is inserted and cannot be specified manually.
  • The auto-added value is unique in the table and will automatically increment in subsequent insert operations.
  • The starting value and incrementing step size of the self-added value can be customized by modifying the table definition.

Please use self-increment constraints to simplify the generation and management of identifiers and ensure the integrity and uniqueness of the data according to the specific table structure and requirements.

Syntax Description

When creating a table, you can define self-increment constraints for a column. Typically, the data type of the autoincrement column is an integer type (such as INT or BIGINT). When creating a table, use the AUTO_INCREMENT keyword to add an incremental constraint to the column.

CREATE TABLE table_name (
  column_name data_type AUTO_INCREMENT,
  ...
  PRIMARY KEY (primary_key_column)
);
  • table_name: The name of the table.
  • column_name: The name of the column to be defined as self-incremented.
  • data_type: The data type of the column, usually an integer type (such as INT or BIGINT).
  • primary_key_column: The primary key column of the table.

Example

Here is an example of creating a table with self-incremented columns:

-- Created a table named `employees` where the `id` column is defined as an autoincrement column. The data type of the `id` column is `INT` and the autoincrement constraint is specified through the `AUTO_INCREMENT` keyword. The primary key of the table is set to the `id` column
CREATE TABLE employees (
  id INT AUTO_INCREMENT,
  name VARCHAR(50),
  department VARCHAR(50),
  PRIMARY KEY (id)
);

-- Insert data and let the autoincrement column automatically generate a unique identification value. Instead of specifying a value for the `id` column, it automatically generates an incremental unique value for the `id` column when inserting data. The value of the `id` column is automatically incremented each time a new row is inserted.

INSERT INTO employees (name, department)
VALUES ('John Doe', 'HR'),
       ('Jane Smith', 'Marketing'),
       ('Mike Johnson', 'IT');

-- The value of the `id` column is automatically incremented and a unique identifier value is generated for each newly inserted row.
mysql> SELECT * FROM employees;
+-------+-------------------------------+
| id | name | department |
+-------+-------------------------------+
| 1 | John Doe | HR |
| 2 | Jane Smith | Marketing |
| 3 | Mike Johnson | IT |
+-------+-------------------------------+
3 rows in set (0.01 sec)

limit

  1. MatrixOne Intelligence does not support the use of ALTER TABLE to modify the starting value and incrementing step size of the value-added value.
  2. In MatrixOne Intelligence, only syntax supports using set @@auto_increment_increment=n to set the incrementing step size, and only syntax supports using set @@auto_increment_offset=n to set the default autoincrement column initial value, but it does not actually take effect; currently, it supports setting the initial value of autoincrement column AUTO_INCREMENT=n, but the step size still defaults to 1.