CHECK CONSTRAINT иногда не срабатывает.

CREATE TABLE link
(
  id serial NOT NULL,
  "group" character varying(250),
  portid1 integer NOT NULL,
  portid2 integer NOT NULL,
  active boolean NOT NULL DEFAULT true,
  CONSTRAINT link_pkey PRIMARY KEY (id),
  CONSTRAINT unique_portid1 CHECK (check_link(portid1, id)),
  CONSTRAINT unique_portid2 CHECK (check_link(portid2, id))
)

CREATE OR REPLACE FUNCTION check_link(portid integer, linkid integer)
  RETURNS boolean AS
$BODY$
DECLARE
  i int;
BEGIN
  IF EXISTS (SELECT * FROM link 
  WHERE (portid1 = portid or portid2=portid) and id!=linkid) THEN
    RETURN false;
  END IF;

  RETURN true;  
END

select * from link where portid1 = 74581 or portid2=74581;
  id   | group | portid1 | portid2 | active
-------+-------+---------+---------+--------
 76628 |       |  113945 |   74581 | t
 76629 |       |  113945 |   74581 | t
 76630 |       |  113945 |   74581 | t
 76632 |       |  113945 |   74581 | t
 76633 |       |  113945 |   74581 | t
 76634 |       |   74581 |  113945 | t
 76645 |       |  113945 |   74581 | t
(7 rows)

postgres=# insert into link (portid1, portid2) values (1,2);
INSERT 0 1

postgres=# insert into link (portid1, portid2) values (2,3);
ОШИБКА:  новая строка в отношении "link" нарушает ограничение-проверку "unique_portid1"
ПОДРОБНОСТИ:  Ошибочная строка содержит (2, null, 2, 3, t).

postgres=# begin transaction;
BEGIN

postgres=# insert into link (portid1, portid2) values (3, 4);
INSERT 0 1

--
-- ПОСЛЕ ЭТОГО В ДРУГОМ КОННЕКТЕ ВЫПОЛНИЛИ ТОТ ЖЕ САМЫЙ INSERT С (3, 4)
--

postgres=# commit;
COMMIT

postgres=# select * from link;
 id | group | portid1 | portid2 | active 
----+-------+---------+---------+--------
  1 |       |       1 |       2 | t
  3 |       |       3 |       4 | t
  4 |       |       3 |       4 | t

create unique index ui_1 on link ((case when portid1<portid2 then portid1 else portid2 end));

create unique index ui_2 on link ((case when portid1>portid2 then portid1 else portid2 end));

create unique index ui_1 on link ((case when portid1<portid2 then portid1 else portid2 end));

create unique index ui_2 on link ((case when portid1>portid2 then portid1 else portid2 end));

CREATE TABLE link
(
  id serial NOT NULL,
  "group" character varying(250),
  portid1 integer NOT NULL,
  portid2 integer NOT NULL,
  active boolean NOT NULL DEFAULT true,
  CONSTRAINT link_pkey PRIMARY KEY (id),
  CONSTRAINT snmp_ports_fkey1 FOREIGN KEY (portid1)
      REFERENCES snmp_ports (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT snmp_ports_fkey2 FOREIGN KEY (portid2)
      REFERENCES snmp_ports (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT portid1_unique UNIQUE (portid1),
  CONSTRAINT portid2_unique UNIQUE (portid2),
  CONSTRAINT portids_check CHECK (portid1 <> portid2)
)

CREATE UNIQUE INDEX portids_unique
  ON link
  USING btree
  ((LEAST(portid1, portid2)), (GREATEST(portid1, portid2)));