CODEWITHSUNDEEP
mysqldatabase-designconstants
xst
xst

Reputation: 3036

MySQL constants table

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?

Upvotes: 4

Views: 3032

Answers (2)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

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.

Upvotes: 5

andrechalom
andrechalom

Reputation: 737

If you want to have several account types, each of them with some details (like a name), and every account should be associated with one account type, you can create your tables like

CREATE TABLE Accounts (
AccountId INT(10) PRIMARY KEY,
AccountType INT(10) NOT NULL, FOREIGN KEY (AccountType) REFERENCES AccountTypes (TypeId),
... 
);

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

This will ensure that all rows added to Accounts will have an AccountType that corresponds to one of the Account Types registered.

Take a look at http://en.wikipedia.org/wiki/Foreign_key

Upvotes: 0

Related Questions