Can my DB?

-- Create a reusable enum type
CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

-- Use it in a table definition
CREATE TABLE orders (
  id       SERIAL PRIMARY KEY,
  status   order_status NOT NULL DEFAULT 'pending'
);

-- Add a new value (can only append, not insert or remove)
ALTER TYPE order_status ADD VALUE 'returned' AFTER 'delivered';

-- CREATE DOMAIN for a constrained scalar type
CREATE DOMAIN positive_int AS INTEGER CHECK (VALUE > 0);
CREATE TABLE products (id SERIAL, quantity positive_int);

• Values are ordered by their definition order — usable in ORDER BY and comparisons.
• ALTER TYPE ADD VALUE can append; removing or reordering values requires recreating the type.
• Stored as 4-byte integers internally; catalog lives in pg_enum / pg_type.

-- ENUM defined inline in the column
CREATE TABLE orders (
  id     INT AUTO_INCREMENT PRIMARY KEY,
  status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') NOT NULL DEFAULT 'pending'
);

-- Related: SET type for multi-value columns
CREATE TABLE user_prefs (
  id    INT AUTO_INCREMENT PRIMARY KEY,
  flags SET('newsletter', 'sms', 'push') NOT NULL DEFAULT ''
);

• Per-column, not reusable. Stored as a 1- or 2-byte integer.
• In non-strict SQL mode, an invalid value silently becomes the empty string. In strict mode it errors.
• Altering the value list is an ALTER TABLE — slow on large tables.
• SET is a related type that stores zero or more values from the defined list.

-- ENUM defined inline in the column
CREATE TABLE orders (
  id     INT AUTO_INCREMENT PRIMARY KEY,
  status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') NOT NULL DEFAULT 'pending'
);

• Same per-column, non-reusable ENUM as MySQL. MariaDB's CREATE TYPE covers SQL/PSM types, not column enums.

-- Workaround: VARCHAR2 column with CHECK constraint
CREATE TABLE orders (
  id     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status VARCHAR2(20) NOT NULL
    CONSTRAINT chk_orders_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

-- Alternative: reference/lookup table with foreign key
CREATE TABLE order_statuses (status VARCHAR2(20) PRIMARY KEY);
INSERT INTO order_statuses VALUES ('pending');
INSERT INTO order_statuses VALUES ('shipped');
-- ... etc
CREATE TABLE orders (
  id     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status VARCHAR2(20) REFERENCES order_statuses(status)
);

• Use VARCHAR2 + CHECK (IN ...), or a lookup table with a foreign key for richer metadata.
• Oracle's CREATE TYPE is for object and collection types, not enums.

-- Workaround: VARCHAR column with CHECK constraint
CREATE TABLE orders (
  id     INT IDENTITY(1,1) PRIMARY KEY,
  status VARCHAR(20) NOT NULL
    CONSTRAINT chk_orders_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

-- Alternative: lookup table with foreign key
CREATE TABLE order_statuses (status VARCHAR(20) PRIMARY KEY);
INSERT INTO order_statuses VALUES ('pending'), ('processing'), ('shipped');
CREATE TABLE orders (
  id     INT IDENTITY(1,1) PRIMARY KEY,
  status VARCHAR(20) REFERENCES order_statuses(status)
);

• Use VARCHAR + CHECK (IN ...), or a lookup table with a foreign key.
• CREATE TYPE creates a type alias, not an enum.

-- Workaround: TEXT column with CHECK constraint
CREATE TABLE orders (
  id     INTEGER PRIMARY KEY,
  status TEXT NOT NULL CHECK(status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

• Use TEXT + CHECK (col IN ...). STRICT tables (3.37+) enforce type affinity but don't add enum semantics.

-- Workaround: VARCHAR column with CHECK constraint
CREATE TABLE orders (
  id     INTEGER IDENTITY(1,1) PRIMARY KEY,
  status VARCHAR(20) NOT NULL
    CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

• VARCHAR + CHECK is the closest you get, but CHECK isn't enforced — validation has to live in the ETL.

-- Workaround: VARCHAR column with CHECK constraint
CREATE TABLE orders (
  id     INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status VARCHAR(20) NOT NULL
    CONSTRAINT chk_orders_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

• Use VARCHAR + CHECK (IN ...). CREATE DISTINCT TYPE makes a distinct type from a base type but doesn't restrict values.

-- Workaround: VARCHAR column with CHECK constraint
-- Note: CHECK constraints are defined but NOT enforced in Snowflake
CREATE TABLE orders (
  id     INTEGER AUTOINCREMENT PRIMARY KEY,
  status VARCHAR(20) NOT NULL
    CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

• CHECK is accepted but not enforced. Validate upstream or via a join to a reference table.

-- No CHECK constraint support. Enforce in application code or during ingestion.
CREATE TABLE dataset.orders (
  id     INT64,
  status STRING
);

-- Validate at query time
SELECT *
FROM dataset.orders
WHERE status NOT IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled');

• No ENUM and no CHECK. Protobuf enum fields via the Storage Write API are the only place true enum semantics show up.