What is automatic incrementation in various…

Auto-increment is a database mechanism that automatically assigns a unique increasing numeric value to a new row. It is often used for creating primary keys.

Implementation depends on the database type:

MySQL: Uses the AUTO_INCREMENT keyword in the column definition.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    price DECIMAL(10, 2)
);

PostgreSQL: Uses SERIAL or BIGSERIAL data types, which are essentially shortcuts for creating a sequence and assigning it as the default value.
```
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50)
);
```

Oracle: Uses sequences and optionally triggers for automatic value assignment.

CREATE SEQUENCE product_id_seq START WITH 1 INCREMENT BY 1;

CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    name VARCHAR2(255),
    price NUMBER(10, 2)
);

-- Optional trigger for automatic assignment
CREATE OR REPLACE TRIGGER products_bi
BEFORE INSERT ON products
FOR EACH ROW
BEGIN
   SELECT product_id_seq.NEXTVAL INTO :NEW.product_id FROM dual;
END;
/

SQL Server (Microsoft SQL Server): Uses the IDENTITY property.

CREATE TABLE Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    OrderDate DATETIME
);

The main difference is how the database manages the generation of the next value. Some (MySQL, SQL Server) integrate it directly into the column definition, while others (PostgreSQL, Oracle) use separate objects (sequences) that can then be linked to a column.

What is automatic incrementation in various databases?