How do I prevent the insert of a unique value in multiple columns-CodePudding

I want to make sure that the insertion of the same value across multiple columns is not possible

Col1 | Col2 
-----|------
ABC  | DEF
XYZ  | ABC

The second row should be prevented because col2 contains the value ABC which is also existing in the first row in col1

How does the CREATE TABLE statement look like to accomplish this?

The DBMS used is POSTGRES

CodePudding user response：

Using a trigger function can be a solution :

CREATE OR REPLACE FUNCTION BeforeInsertUpdate_mytable () RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF EXISTS (SELECT * FROM mytable WHERE Col1 = NEW.Col1 OR Col2 = NEW.Col1 OR Col2 = NEW.Col1 OR Col2 = NEW.Col2)
  THEN RETURN NULL ;
  ELSE RETURN NEW ;
  END IF ;
END ; $$

CREATE OR REPLACE TRIGGER BeforeInsertUpdate_mytable BEFORE INSERT OR UPDATE OF Col1, Col2 ON mytable FOR EACH ROW EXECUTE FUNCTION BeforeInsertUpdate_mytable () ;