Postgresql – Is there Such Thing As The Opposite of Unique Constraint

constraintpostgresql

I want to be constrain my database with what is essentially the opposite of a UNIQUE constraint. I want to only be able to add a new row if some combination of fields has been added before.

Note: I am working within postgres.

Take the following datasets for example. I want a "NON UNIQUE" constraint on the following:

1, a, 1

1, a, 1

1, b, 1

I should not have been able to add the row (1, b, 1) since it has not appeared before. There are also other fields I don't want to constrain so it's not like all the entries are identical.

This may be a separate question, but I would also like to add the constraint that all rows with the same value in one column (i.e. a in the above example) have the same value in another.

So I want this to be impossible

1, a, 1

1, a, 1

0, a, 1

But this is ok

1, a, 1

1, a, 1

0, b, 1

I have looked around pretty heavily and the opposite of this is possible
with unique constraints, but I can't find anything that works for this.
Check constraints will only look at the row in question, and won't take into account the entire table's entries like a unique constraint would.

Is this even possible? Or is this now out of the realms of Postgres functionality, and must be done in-application.

Best Answer

Subqueries are not allowed in a CHECK constraint, or in the WHEN clause of a trigger, but you can use them inside a trigger function, something like this:

CREATE FUNCTION my_little_trigger_function()
RETURNS TRIGGER
AS $$
DECLARE
   row_count int;
BEGIN
   row_count := (SELECT COUNT(*)
                 FROM ...
                 WHERE ... = NEW.x ...);
   IF (row_count = 0) THEN
       RAISE EXCEPTION 'I''m sorry, Dave. I''m afraid I can''t do that.';
   END IF;
   RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER my_little_trigger
BEFORE INSERT ON my_little_table
FOR EACH ROW
EXECUTE PROCEDURE my_little_trigger_function();

Related Solutions

Mysql – Creating a partial unique constraint for MySQL

The only option that comes to mind is a BEFORE INSERT trigger. In 5.5 you could use SIGNAL:

DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
  IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo 
     WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
  THEN
    SIGNAL SQLSTATE '45000'
     SET MESSAGE_TEXT = 'Cannot insert this item due to unique constraint', 
         MYSQL_ERRNO = 1001;
  END IF;
END$$
DELIMITER ;

[sqlfiddle]

But pre 5.5, you would do this something like this:

DELIMITER $$
CREATE TRIGGER bi_foo BEFORE INSERT on foo
FOR EACH ROW
BEGIN
  IF (ISNULL(NEW.col3) AND (SELECT COUNT(*) FROM foo 
     WHERE col1 = NEW.col1 AND col2 = NEW.col2 AND col3 IS NULL) > 0)
  THEN
    SET NEW='Error: Cannot insert this item due to unique constraint';
  END IF;
END$$
DELIMITER ;

[sqlfiddle]

Sql-server – Unique constraint across two columns

This isn't possible with the proposed table structure declaratively. You would need triggers to enforce this.

A unique index on both columns, together with a pair of check constraints with scalar UDFs, gets quite close however.

CREATE TABLE UserProfile
  (
     Id             INT PRIMARY KEY,
     PrimaryEmail   VARCHAR(100),
     SecondaryEmail VARCHAR(100)
  )

CREATE UNIQUE INDEX IX1
  ON UserProfile(PrimaryEmail)

CREATE UNIQUE INDEX IX2
  ON UserProfile(SecondaryEmail)

go

CREATE FUNCTION dbo.EmailInUseAsPrimary (@Email VARCHAR(100))
RETURNS BIT
AS
  BEGIN
      RETURN
        (SELECT COUNT(*)
         FROM   UserProfile WITH (READCOMMITTEDLOCK)
         WHERE  PrimaryEmail = @Email)
  END;

go

CREATE FUNCTION dbo.EmailInUseAsSecondary (@Email VARCHAR(100))
RETURNS BIT
AS
  BEGIN
      RETURN
        (SELECT COUNT(*)
         FROM   UserProfile WITH (READCOMMITTEDLOCK)
         WHERE  SecondaryEmail = @Email)
  END;

GO

ALTER TABLE UserProfile
  ADD CHECK ( dbo.EmailInUseAsPrimary(SecondaryEmail) = 0), 
      CHECK ( dbo.EmailInUseAsSecondary(PrimaryEmail) = 0)

The reason for READCOMMITTEDLOCK is to avoid problems with snapshot isolation.

One problem with the approach above is that because the constraints are evaluated RBAR it can fail some transactions that ought to succeed.

For the example data

INSERT INTO UserProfile
VALUES (1, 'abc@abc.com', 'def@def.com'),
       (2, 'ghi@ghi.com', 'jkl@jkl.com')

This statement fails

UPDATE UserProfile
SET PrimaryEmail = CASE Id WHEN 1 THEN 'jkl@jkl.com' WHEN 2 THEN 'def@def.com' END,
    SecondaryEmail = CASE Id WHEN 1 THEN 'ghi@ghi.com' WHEN 2 THEN 'abc@abc.com' END

even though at the end of the transaction the constraints would have been met. But maybe it is sufficiently unlikely that you will be performing this kind of update (swapping email addresses between both type and person) that this can be ignored.

Best Answer

Related Solutions

Mysql – Creating a partial unique constraint for MySQL

Sql-server – Unique constraint across two columns

Related Question