Home   database  

How to store enum values in database

Storing enums in a database table typically involves mapping each enum value to a corresponding database column. Different database systems handle enums in slightly different ways. Here’s a general approach using SQL and then specifically with examples for MySQL and PostgreSQL:

General Approach

  1. Define Enum in Application: Define your enum in your application code. For example, in JavaScript or TypeScript:

    const UserRole = {
        ADMIN: 'admin',
        USER: 'user',
        MODERATOR: 'moderator'
    };
    
  2. Map Enum to Database Table:

    • Option 1: Integer Mapping: Map each enum value to an integer in the database table.
    • Option 2: String Mapping: Map each enum value directly to a string column in the database.

Example with MySQL

In MySQL, you can use ENUM to define a column that accepts one of a predefined set of string values:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    role ENUM('admin', 'user', 'moderator') NOT NULL
);

In this example:

Example with PostgreSQL

In PostgreSQL, you can use ENUM types or a check constraint to achieve similar functionality:

Using ENUM type:

CREATE TYPE user_role AS ENUM ('admin', 'user', 'moderator');

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    role user_role NOT NULL
);

Using check constraint:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL,
    CONSTRAINT role_check CHECK (role IN ('admin', 'user', 'moderator'))
);

Benefits of Using ENUM

Published on: Jul 10, 2024, 03:10 AM  
 

Comments

Add your comment