473,385 Members | 1,445 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,385 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 1812

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...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

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.