Mastering Enum SQL Example: A Complete Guide

Enums in SQL are a convenient way to define and use well-known constants, ensuring data integrity and improved code readability. Let's delve into using enums in SQL with practical examples.

SQL SUM() Explained with Examples | Beginner SQL Tutorial

Enums, short for enumerations, allow you to define a list of values that a particular column can take. This helps maintain data consistency and simplifies coding. Now, let's explore their application.

SQL Joins Explained with Examples | Complete JOIN Cheat Sheet for Beginners

Defining Enums in SQL

To define an enum in SQL, you use the ENUM data type. Here's a basic example:

SQL NULLIF() Explained with Examples | Prevent Divide by Zero Errors in SQL

Let's assume we're creating a database for a bookstore. We can create an enum for 'Category' to include types like 'Fiction', 'Non-Fiction', 'Children', and 'Reference'.

Creating an Enum Column

7 SQL Projects for Beginners | Build a Real Data Portfolio

Here's how you can create a table using enum for the 'Category' column:

CREATE TABLE Books
(
    ID INT PRIMARY KEY,
    Title VARCHAR(100),
    Category ENUM('Fiction', 'Non-Fiction', 'Children', 'Reference')
);

In this SQL statement, 'Category' is defined as an enum with four possible values.

Inserting and Selecting Data using Enum

SQL Basics Tutorial for Beginners – Structured Query Language Commands, Syntax & Examples Guide

Now, let's insert some data into the 'Books' table:

INSERT INTO Books (ID, Title, Category) VALUES
(1, 'To Kill a Mockingbird', 'Fiction'),
(2, 'Harry Potter and the Philosopher’s Stone', 'Children'),
(3, 'Sapiens: A Brief History of Humankind', 'Non-Fiction');

And here's how you can select the data:

SELECT * FROM Books;

Using Enums with Check Constraints

a sheet of paper that has some notes on it with words and symbols in the middle

You can also use enums to enforce data consistency through check constraints. Here's how you can do it:

Let's assume we have a 'Users' table with a 'Role' enum. We want to ensure the 'Role' column can only contain 'Admin', 'Staff', or 'Customer' values.

7 SQL QUERIES EVERY STUDENT SHOULD MASTER

SQL Joins Explained 🚀 | Beginner Data Analyst SQL Guide

SQL ORDER BY Explained with Examples | Sort Data in SQL

TypeScript Enums vs Union Types: Which Should You Use?

Learn SQL just by investing 30 minutes a day within 30 Days

SQL Joins Explained with Examples | Beginner SQL Guide

SQL INNER JOIN Explained with Examples | Beginner SQL Tutorial

SQL GROUP BY Explained Visually (Beginner-Friendly Guide)

Complete SQL Cheat Sheet for Beginners | SQL Commands, Joins, Queries & Database Guide

#sql #dataanalytics #powerbi #datavisualization #businessintelligence… | Vipin Chandra | 12 comments

SQL TRIM() Explained with Examples | Remove Extra Spaces in SQL

SQL RIGHT() Explained with Examples | Extract Characters from the Right in SQL Server

SQL ISNULL() Explained with Examples | Replace NULL Values in SQL Server

Creating an Enum with Check Constraint

CREATE TABLE Users
(
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Role ENUM('Admin', 'Staff', 'Customer') CHECK (Role IN ('Admin', 'Staff', 'Customer'))
);

In this SQL statement, the check constraint ensures the 'Role' column can only contain one of the three enum values.

Trying to Violate the Check Constraint

If you try to insert a value that's not in the enum, you'll get an error:

INSERT INTO Users (ID, Name, Role) VALUES (1, 'John Doe', 'Guest');

This will raise an error, protecting the data integrity of your database.

In conclusion, using enums in SQL provides numerous benefits, from improved code readability to enhanced data consistency. They're an invaluable tool for any SQL developer. Now, go ahead and harness the power of enums in your next SQL project!