Can I put constraint on column without referring to another table?

Question

I have a text column that should only have 1 of 3 possible strings. To put a constraint on it, I would have to reference another table. Can I instead put the values of the constraint directly on the column without referring to another table?

Answer 1

If this is SQL Server, Oracle, or PostgreSQL, yes, you can use a check constraint.

If it's MySQL, check constraints are recognized but not enforced. You can use an enum, though. If you need a comma-separated list, you can use a set.

However, this is generally frowned upon, since it's definitely not easy to maintain. Just best to create a lookup table and ensure referential integrity through that.

Answer 2

In Oracle, SQL Server and PostgreSQL, use CHECK constraint.

CREATE TABLE mytable (myfield INT VARCHAR(50) CHECK (myfield IN ('first', 'second', 'third'))

In MySQL, use ENUM datatype:

CREATE TABLE mytable (myfield ENUM ('first', 'second', 'third'))

Answer 3

In addition to the CHECK constraint and ENUM data type that other mention, you could also write a trigger to enforce your desired restriction.

I don't necessarily recommend a trigger as a good solution, I'm just pointing out another option that meets your criteria of not referencing a lookup table.

My habit is to define lookup tables instead of using constraints or triggers, when the rule is simply to restrict a column to a finite set of values. The performance impact of checking against a lookup table is no worse than using CHECK constraints or triggers, and it's a lot easier to manage when the set of values might change from time to time.

Also a common task is to query the set of permitted value, for instance to populate a form field in the user interface. When the permitted values are in a lookup table, this is a lot easier than when they're defined in a list of literal values in a CHECK constraint or ENUM definition.

---

Re comment "how exactly to do lookup without id"

CREATE TABLE LookupStrings (
  string VARCHAR(20) PRIMARY KEY
);

CREATE TABLE MainTable (
  main_id INT PRIMARY KEY,
  string VARCHAR(20) NOT NULL,
  FOREIGN KEY (string) REFERENCES LookupStrings (string)
);

Now you can be assured that no value in MainTable.string is invalid, since the referential integrity prevents that. But you don't have to join to the LookupStrings table to get the string, when you query MainTable:

SELECT main_id, string FROM MainTable;

See? No join! But you get the string value.

---

Re comment about multiple foreign key columns:

You can have two individual foreign keys, each potentially pointing to different rows in the lookup table. The foreign key column doesn't have to be named the same as the column in the referenced table.

My common example is a bug-tracking database, where a bug was reported by one user, but assigned to be fixed by a different user. Both reported_by and assigned_to are foreign keys referencing the Accounts table.

CREATE TABLE Bugs (
  bug_id INT PRIMARY KEY,
  reported_by INT NOT NULL,
  assigned_to INT,
  FOREIGN KEY (reported_by) REFERENCES Accounts (account_id),
  FOREIGN KEY (assigned_to) REFERENCES Accounts (account_id)
);