Can I set a constraint on a column without referring to another table?

In addition to the CHECK constraint and the ENUM datatype mentioned elsewhere, you can also write a trigger to enforce the constraint you require.

I do not necessarily recommend the trigger as a good solution, I just provide another option that suits your criteria without referring to the lookup table.

My habit is to define lookup tables instead of using constraints or triggers, where the rule simply restricts the column to a finite set of values. The performance impact on lookup table validation is as good as using CHECK constraints or triggers, and is much easier to manage when the set of values ​​may change from time to time.

It is also a common task to query for a set of valid values, for example, to fill out a form field in the user interface. When valid values ​​are in the lookup table, it is much easier than when they are defined in a literal list in a CHECK constraint or an ENUM definition.

Re comment "how to exactly search 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 sure that the value is MainTable.string

not valid, as referential integrity prevents this from happening. But you don't need to join the table LookupStrings

to get the row when you ask MainTable

:

SELECT main_id, string FROM MainTable;

      

        
        
        
      

    

Cm? Join us! But you will get a string value.

Comment out multiple foreign key columns:

You can have two separate foreign keys, each of which can point to a different row in the lookup table. The foreign key column should not be named the same as the column in the referenced table.

My general example is a bug tracking database where a bug was reported by one user but assigned to be fixed by another user. Both reported_by

and assigned_to

are foreign keys referencing the table Accounts

.

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)
);