472,378 Members | 1,299 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,378 software developers and data experts.

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),
) 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?
Nov 11 '05 #1
0 1766

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: RAD | last post by:
I am working with an evaluation copy of SQL Server 2000 for the first time; my DB experience lies with MS Access. I have a simple table in SQL Server (tblCompany) that has a field called...
1
by: Dalan | last post by:
I apparently need some assistance with Access 97 regarding the changing of a value in a field through use of a check box. I have a subform (sfrmDocList) with a column for indicating if a document...
1
by: icetalks | last post by:
have a look at this code , its for logging the user in after checking his UserName and Password. dim check as boolean = false ... ... If txtUserName.Text.Length = 0 And txtPass.Text.Length =...
1
by: Spectre1337 | last post by:
Hello, it seems like the check constraint validation of MS SQL Server Management Studio express is horribly, horribly broken. Either that or I'm using it wrong. I hope it's the latter. I'm...
1
by: Java Kumar | last post by:
Hi Friends, I have a form which contains elements such as check boxes,text box,text area ., Problem is in Check boxes. By default, Check boxes are unchecked and text boxes...
1
by: VikasPradhan | last post by:
Hi all, i am doing application in c#.. can anyone tell how can i uncheck all check boxes of List View Thank You
2
by: deepika1 | last post by:
hi , i wrote a code for to delete the particular rows in a table using check boxes.But in that i select the particular rows and click on the delete button these rows didn't delete.plz check...
1
by: thesti | last post by:
hi, i have a field named 'nim', in my table which is a char(10) field. i want to create a constraint that checks the field so the field must be 10 characters long and each character is a...
21
by: rashgang | last post by:
no check box selected when i gave delete all link the error is coming <?php include "includes/connection.php"; include "includes/Functions_category.php"; include...
2
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and efficiency. While initially associated with cryptocurrencies...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
2
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
1
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
0
DizelArs
by: DizelArs | last post by:
Hi all) Faced with a problem, element.click() event doesn't work in Safari browser. Tried various tricks like emulating touch event through a function: let clickEvent = new Event('click', {...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.