How to Create an Auto-Increment Primary Key in PostgreSQL

When working with a PostgreSQL database, you may need to define a primary key column that auto-increments its value for each new record. This can be achieved by using the SERIAL type combined with the PRIMARY KEY constraint. The following steps outline how to accomplish this:

1. Start by creating a new table, for example, let’s call it cars. Specify the columns you want in your table definition, including the primary key column.

CREATE TABLE cars (
   id SERIAL PRIMARY KEY,
   brand VARCHAR(30) NOT NULL,
   model VARCHAR(30) NOT NULL,
   year CHAR(4) NOT NULL
);

2. In this example, the id column is defined as SERIAL, which creates an auto-incrementing integer column. The PRIMARY KEY constraint ensures that the id column is unique and serves as the primary key for the table.

3. You can replace the VARCHAR(30) and CHAR(4) in the example with the appropriate data types and lengths that match your specific requirements.

To achieve a similar effect in MySQL or MariaDB, you would use the AUTO_INCREMENT attribute instead of SERIAL. Here is the equivalent code:

CREATE TABLE cars (
   id INT(11) NOT NULL AUTO_INCREMENT,
   brand VARCHAR(30) NOT NULL,
   model VARCHAR(30) NOT NULL,
   year CHAR(30) NOT NULL,
   PRIMARY KEY (`id`)
);

By following these steps, you can define an auto-increment primary key column in PostgreSQL or its equivalent in MySQL/MariaDB. This allows for easy and efficient management of unique identifiers for your database records.