469,573 Members | 1,684 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,573 developers. It's quick & easy.

Problem wih a CONSTRAINT CHECK not doing its job

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),

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
actorID ALIAS FOR $1;
contactTypeID ALIAS FOR $2;
countage SMALLINT;
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;
RETURN true;
' 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
(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?
Nov 11 '05 #1
0 1678

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Java Kumar | last post: by
2 posts views Thread by deepika1 | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.