MySQL constants table

Question

How can I create a constants table in mysql that will match a type (using an int to differentiate among different types) with the full name of the type. The constants table should be read only and is not expected to ever need changing or updating only reading.

CREATE TABLE Accounts (
    AccountType INT,
    ...
);

CREATE TABLE AccountTypes (
    TypeId INT,
    TypeName VARCHAR(255),
);

In Java there is ImmutableMap<Integer><String> (in Guava), or Enums with integer instance variables, and in C++ there is also enum or static const string[...] ... = { ... } where the type integer is the index.

Is this possible, or is there a better alternative?

Answer 1

Looks like you need something similar to this:

CREATE TABLE AccountType (
    TypeId INT PRIMARY KEY,
    TypeName VARCHAR(255),
    TypeShortName VARCHAR(255)
);

INSERT INTO AccountType VALUES
    (1, 'Foo', 'F'),
    (2, 'Bar', 'Br'),
    (3, 'Baz', 'Bz');

CREATE TRIGGER AccountTypeInsertTrigger BEFORE INSERT ON AccountType FOR EACH ROW
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Cannot INSERT - the AccountType table is read-only.';

CREATE TRIGGER AccountTypeUpdateTrigger BEFORE UPDATE ON AccountType FOR EACH ROW
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Cannot UPDATE - the AccountType table is read-only.';

CREATE TRIGGER AccountTypeDeleteTrigger BEFORE DELETE ON AccountType FOR EACH ROW
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Cannot DELETE - the AccountType table is read-only.';

Essentially, you just define a normal table and fill it with data, then use triggers to forbid any changes.

According to MySQL documentation: To signal a generic SQLSTATE value, use '45000', which means “unhandled user-defined exception.”

---

Alternatively, REVOKE all privileges except SELECT from appropriate users.