SQL: Is there a way to validate the value from a column against the value from a column in a different table NOT using Foreign Key?

Question

I want a column from a table to have a limited domain, which is listed in a different table. Usually, I'd just do this:

ALTER TABLE Table1
ADD CONSTRAINT FK_Especialidade FOREIGN KEY (column1)
REFERENCES Table2

However the values that "column1" must reference should not be a Primary Key in Table2. Any suggestions?

Answer 1

How about a trigger?

Answer 2

You could use a trigger (this is off the top of my head, the syntax might not be right...)

CREATE TRIGGER MyTrigger ON Table1 FOR INSERT, UPDATE
AS
  IF UPDATE(column1)
  BEGIN
    IF NOT EXIST (SELECT column2 FROM Table2 WHERE column2 = column1)
      RAISERROR ('Invalid column1 value.', 16, 1)
    END
  END

Make sure you at least have an index on column2 in Table2, you want your triggers to execute as fast as possible!

Answer 3

A foreign key can reference any UNIQUE constraint in the target table, not just the primary key.

CREATE TABLE foo (
  foo_id INT PRIMARY KEY,
  some_attribute INT,
  UNIQUE KEY (some_attribute)
);

CREATE TABLE bar (
  bar_id INT PRIMARY KEY,
  some_attribute INT,
  FOREIGN KEY (some_attribute) REFERENCES foo (some_attribute) 
);

Answer 4

You've only added a 'SQL' tag so here's some valid full SQL-92:

ALTER TABLE Table1
ADD CONSTRAINT ck_Especialidade 
CHECK (
       EXISTS (
               SELECT * 
                 FROM Table2 
                WHERE Table2.column1 = Table1.column1
              )
      );

Of course, not all SQL products implement this full SQL-92 feature (e.g. SQL Server doesn't...) though there may be a workaround (... e.g. employing a user defined function). Anyhow, why reinvent the wheel?