Problem with a constraint check on a table.
От | Fabre Lambeau |
---|---|
Тема | Problem with a constraint check on a table. |
Дата | |
Msg-id | 00e101c34e1b$4b2e12a0$0200a8c0@calliope обсуждение исходный текст |
Ответы |
Re: Problem with a constraint check on a table.
|
Список | pgsql-general |
Hi ! I've got a problem when adding a CONSTRAINT CHECK on a table by calling a function. It just seems not to work... Here is the table (simplified to only the relevant fields for this case): CREATE TABLE public.tb_contacts ( contact_id serial NOT NULL, actor_id varchar(50) NOT NULL, contacttype_id varchar(6) NOT NULL, contact varchar NOT NULL, contact_principal bool NOT NULL DEFAULT true, contact_validity bool DEFAULT true, CONSTRAINT pk_contact PRIMARY KEY (contact_id), ) WITH OIDS; I created a function to check whether there is more than one record for a given actor_id and contacttype_id that has both contact_principal and contact_validity as 'true'. CREATE FUNCTION public.is_principalcontact_unique(varchar, varchar) RETURNS bool AS ' DECLARE actorID ALIAS FOR $1; contactTypeID ALIAS FOR $2; countage SMALLINT; BEGIN SELECT INTO countage count(contact_principal) FROM tb_contacts WHERE actor_id = actorID AND contacttype_id = contactTypeID AND contact_validity = true AND contact_principal = true GROUP BY actor_id, contacttype_id; IF countage > 1 THEN RETURN false; END IF; RETURN true; END; ' LANGUAGE 'plpgsql' STABLE; When testing, this function seems to work. I then added a constraint using that function, to make sure no new record can be added that would violate that constraint. ALTER TABLE tb_contacts ADD CONSTRAINT CKC_UNIQUE_PRINCIPAL CHECK (is_principalcontact_unique(actor_id, contacttype_id) = true) I then tried to add a new record, duplicating another one with both contact_validity and contact_principal being 'true' (I changed the contact_id, obviously, to avoid duplicate entries in primary key). PostgreSQL let me insert it, without raising an error, although the function is_principalcontact_unique(actor_id, contacttype_id) now returns 'false' Any idea why it is so? Fabre Lambeau Cambridge University Computer Laboratory
В списке pgsql-general по дате отправления: