473,322 Members | 1,714 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,322 software developers and data experts.

Pseudo Check Constraint

Consider the following tables:

CREATE TABLE "AIMD "."CHANNELSESSION" (
"CHANNELSESSIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY
( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
"CONNECTSTARTTIME" TIMESTAMP NOT NULL ,
"CONNECTENDTIME" TIMESTAMP ,
"ACTIVESESSION" VARCHAR(1),
"CHANNELID" DECIMAL(12,0) NOT NULL )
IN "USERSPACE1" ;
CREATE TABLE "AIMD "."AIMCONNECTION" (
"AIMCONNECTIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY
( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
"BRANCHID" VARCHAR(24) ,
"USERID" VARCHAR(24) ,
"STARTDATETIME" TIMESTAMP NOT NULL ,
"ENDDATETIME" TIMESTAMP ,
"TERMINATIONREASON" VARCHAR(24) ,
"BUSINESSLINE" VARCHAR(24) ,
"CHANNELSESSIONID" DECIMAL(13,0) NOT NULL )
IN "USERSPACE1" ;

There is a froeiegn key relationship between these two tables:
CHANNELSESSION.CHANNELSESSIONID parent key and froeign key in
AIMCONNECTION.

My question is this: is it possible to create a check constraint that
double checks the value of an attribute that is not a part of the
foreing key. For instance, there may be an existing parent key in
CHANNELSESSION, but if the value of ACTIVESESSION in the corresponding
row is "N", then the row should not be able to be inserted.

Hope that made sense.
Nov 12 '05 #1
5 2411
The easiest way to implement RI-type things that aren't quite
referential integrity is usually with a trigger.

Anthony Robinson wrote:
Consider the following tables:

CREATE TABLE "AIMD "."CHANNELSESSION" (
"CHANNELSESSIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY
( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
"CONNECTSTARTTIME" TIMESTAMP NOT NULL ,
"CONNECTENDTIME" TIMESTAMP ,
"ACTIVESESSION" VARCHAR(1),
"CHANNELID" DECIMAL(12,0) NOT NULL )
IN "USERSPACE1" ;
CREATE TABLE "AIMD "."AIMCONNECTION" (
"AIMCONNECTIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY
( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
"BRANCHID" VARCHAR(24) ,
"USERID" VARCHAR(24) ,
"STARTDATETIME" TIMESTAMP NOT NULL ,
"ENDDATETIME" TIMESTAMP ,
"TERMINATIONREASON" VARCHAR(24) ,
"BUSINESSLINE" VARCHAR(24) ,
"CHANNELSESSIONID" DECIMAL(13,0) NOT NULL )
IN "USERSPACE1" ;

There is a froeiegn key relationship between these two tables:
CHANNELSESSION.CHANNELSESSIONID parent key and froeign key in
AIMCONNECTION.

My question is this: is it possible to create a check constraint that
double checks the value of an attribute that is not a part of the
foreing key. For instance, there may be an existing parent key in
CHANNELSESSION, but if the value of ACTIVESESSION in the corresponding
row is "N", then the row should not be able to be inserted.

Hope that made sense.


Nov 12 '05 #2
Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<bv**********@hanover.torolab.ibm.com>...
The easiest way to implement RI-type things that aren't quite
referential integrity is usually with a trigger.

Anthony Robinson wrote:
Consider the following tables:

CREATE TABLE "AIMD "."CHANNELSESSION" (
"CHANNELSESSIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY
( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
"CONNECTSTARTTIME" TIMESTAMP NOT NULL ,
"CONNECTENDTIME" TIMESTAMP ,
"ACTIVESESSION" VARCHAR(1),
"CHANNELID" DECIMAL(12,0) NOT NULL )
IN "USERSPACE1" ;
CREATE TABLE "AIMD "."AIMCONNECTION" (
"AIMCONNECTIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY
( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
"BRANCHID" VARCHAR(24) ,
"USERID" VARCHAR(24) ,
"STARTDATETIME" TIMESTAMP NOT NULL ,
"ENDDATETIME" TIMESTAMP ,
"TERMINATIONREASON" VARCHAR(24) ,
"BUSINESSLINE" VARCHAR(24) ,
"CHANNELSESSIONID" DECIMAL(13,0) NOT NULL )
IN "USERSPACE1" ;

There is a froeiegn key relationship between these two tables:
CHANNELSESSION.CHANNELSESSIONID parent key and froeign key in
AIMCONNECTION.

My question is this: is it possible to create a check constraint that
double checks the value of an attribute that is not a part of the
foreing key. For instance, there may be an existing parent key in
CHANNELSESSION, but if the value of ACTIVESESSION in the corresponding
row is "N", then the row should not be able to be inserted.

Hope that made sense.


I've been looknig for examples of a BEFORE INSERT trigger and can't
seem to find any godd ones. Any ideas where to look?
Nov 12 '05 #3
There's one BEFORE example under CREATE TRIGGER in the SQL Reference
(you can search the docs on STOCK_STATUS). There are trigger articles
here as well:

http://www-136.ibm.com/developerworks/db2/

But it might take some hunting to find lots of BEFORE examples.

Anthony Robinson wrote:
Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<bv**********@hanover.torolab.ibm.com>...
The easiest way to implement RI-type things that aren't quite
referential integrity is usually with a trigger.

Anthony Robinson wrote:

Consider the following tables:

CREATE TABLE "AIMD "."CHANNELSESSION" (
"CHANNELSESSIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY
( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
"CONNECTSTARTTIME" TIMESTAMP NOT NULL ,
"CONNECTENDTIME" TIMESTAMP ,
"ACTIVESESSION" VARCHAR(1),
"CHANNELID" DECIMAL(12,0) NOT NULL )
IN "USERSPACE1" ;
CREATE TABLE "AIMD "."AIMCONNECTION" (
"AIMCONNECTIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY
( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
"BRANCHID" VARCHAR(24) ,
"USERID" VARCHAR(24) ,
"STARTDATETIME" TIMESTAMP NOT NULL ,
"ENDDATETIME" TIMESTAMP ,
"TERMINATIONREASON" VARCHAR(24) ,
"BUSINESSLINE" VARCHAR(24) ,
"CHANNELSESSIONID" DECIMAL(13,0) NOT NULL )
IN "USERSPACE1" ;

There is a froeiegn key relationship between these two tables:
CHANNELSESSION.CHANNELSESSIONID parent key and froeign key in
AIMCONNECTION.

My question is this: is it possible to create a check constraint that
double checks the value of an attribute that is not a part of the
foreing key. For instance, there may be an existing parent key in
CHANNELSESSION, but if the value of ACTIVESESSION in the corresponding
row is "N", then the row should not be able to be inserted.

Hope that made sense.

I've been looknig for examples of a BEFORE INSERT trigger and can't
seem to find any godd ones. Any ideas where to look?


Nov 12 '05 #4
Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<bv**********@hanover.torolab.ibm.com>...
There's one BEFORE example under CREATE TRIGGER in the SQL Reference
(you can search the docs on STOCK_STATUS). There are trigger articles
here as well:

http://www-136.ibm.com/developerworks/db2/

But it might take some hunting to find lots of BEFORE examples.

Anthony Robinson wrote:
Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<bv**********@hanover.torolab.ibm.com>...
The easiest way to implement RI-type things that aren't quite
referential integrity is usually with a trigger.

Anthony Robinson wrote:
Consider the following tables:

CREATE TABLE "AIMD "."CHANNELSESSION" (
"CHANNELSESSIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY
( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
"CONNECTSTARTTIME" TIMESTAMP NOT NULL ,
"CONNECTENDTIME" TIMESTAMP ,
"ACTIVESESSION" VARCHAR(1),
"CHANNELID" DECIMAL(12,0) NOT NULL )
IN "USERSPACE1" ;
CREATE TABLE "AIMD "."AIMCONNECTION" (
"AIMCONNECTIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY
( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
"BRANCHID" VARCHAR(24) ,
"USERID" VARCHAR(24) ,
"STARTDATETIME" TIMESTAMP NOT NULL ,
"ENDDATETIME" TIMESTAMP ,
"TERMINATIONREASON" VARCHAR(24) ,
"BUSINESSLINE" VARCHAR(24) ,
"CHANNELSESSIONID" DECIMAL(13,0) NOT NULL )
IN "USERSPACE1" ;

There is a froeiegn key relationship between these two tables:
CHANNELSESSION.CHANNELSESSIONID parent key and froeign key in
AIMCONNECTION.

My question is this: is it possible to create a check constraint that
double checks the value of an attribute that is not a part of the
foreing key. For instance, there may be an existing parent key in
CHANNELSESSION, but if the value of ACTIVESESSION in the corresponding
row is "N", then the row should not be able to be inserted.

Hope that made sense.

I've been looknig for examples of a BEFORE INSERT trigger and can't
seem to find any godd ones. Any ideas where to look?


Blair:

I've been tinkering around with the whole triggerthing and cam up with
this:

CONNECT TO AIM2;
CREATE TRIGGER AIMP.VALIDATE NO CASCADE BEFORE INSERT ON
AIMD.AIMCONNECTION REFERENCING NEW AS NEWROWS FOR EACH ROW MODE
DB2SQL WHEN (NEWROWS.CHANNELSESSIONID =
CHANNELSESSION.CHANNELSESSIONID)
BEGIN ATOMIC
IF CHANNELSESSION.ACTIVESESSION = 'N' THEN
SIGNAL SQLSTATE '70001' ('NO VALID CHANNEL SESSION EXISTS');
END IF;
END;
CONNECT RESET;

When I try to create it I get an error stating that
CHANNELSESSION.CHANNELSESSIONID is not valid in the context being
used.

Hopefully you or someone can shed some light on how I can go about
accomplishing this:

Before a row is inserted into AIMCONNECTION, I want to check the
parent row in the CHANNELSESSION table (foreign key is
CHANNELSESSIONID). If the value of ACTIVESESSION is 'N', then raise an
error and not insert the new row. If the value is 'Y', then go ahead
and insert the row.

Is my syntax totally off? Or do I need to use an INSTEAD OF trigger
instead?

Thanks again for any assistance.
Nov 12 '05 #5
Anthony Robinson wrote:
Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<bv**********@hanover.torolab.ibm.com>...
There's one BEFORE example under CREATE TRIGGER in the SQL Reference
(you can search the docs on STOCK_STATUS). There are trigger articles
here as well:

http://www-136.ibm.com/developerworks/db2/

But it might take some hunting to find lots of BEFORE examples.

Anthony Robinson wrote:

Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<bv**********@hanover.torolab.ibm.com>...
The easiest way to implement RI-type things that aren't quite
referential integrity is usually with a trigger.

Anthony Robinson wrote:

>Consider the following tables:
>
>CREATE TABLE "AIMD "."CHANNELSESSION" (
>"CHANNELSESSIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY
>( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
>"CONNECTSTARTTIME" TIMESTAMP NOT NULL ,
>"CONNECTENDTIME" TIMESTAMP ,
>"ACTIVESESSION" VARCHAR(1),
>"CHANNELID" DECIMAL(12,0) NOT NULL )
>IN "USERSPACE1" ;
>
>
>CREATE TABLE "AIMD "."AIMCONNECTION" (
>"AIMCONNECTIONID" DECIMAL(13,0) NOT NULL GENERATED ALWAYS AS IDENTITY
>( START WITH +1 , INCREMENT BY +1 , CACHE 20 ) ,
>"BRANCHID" VARCHAR(24) ,
>"USERID" VARCHAR(24) ,
>"STARTDATETIME" TIMESTAMP NOT NULL ,
>"ENDDATETIME" TIMESTAMP ,
>"TERMINATIONREASON" VARCHAR(24) ,
>"BUSINESSLINE" VARCHAR(24) ,
>"CHANNELSESSIONID" DECIMAL(13,0) NOT NULL )
>IN "USERSPACE1" ;
>
>There is a froeiegn key relationship between these two tables:
>CHANNELSESSION.CHANNELSESSIONID parent key and froeign key in
>AIMCONNECTION.
>
>My question is this: is it possible to create a check constraint that
>double checks the value of an attribute that is not a part of the
>foreing key. For instance, there may be an existing parent key in
>CHANNELSESSION, but if the value of ACTIVESESSION in the corresponding
>row is "N", then the row should not be able to be inserted.
>
>Hope that made sense.
I've been looknig for examples of a BEFORE INSERT trigger and can't
seem to find any godd ones. Any ideas where to look?

Blair:

I've been tinkering around with the whole triggerthing and cam up with
this:

CONNECT TO AIM2;
CREATE TRIGGER AIMP.VALIDATE NO CASCADE BEFORE INSERT ON
AIMD.AIMCONNECTION REFERENCING NEW AS NEWROWS FOR EACH ROW MODE
DB2SQL WHEN (NEWROWS.CHANNELSESSIONID =
CHANNELSESSION.CHANNELSESSIONID)
BEGIN ATOMIC
IF CHANNELSESSION.ACTIVESESSION = 'N' THEN
SIGNAL SQLSTATE '70001' ('NO VALID CHANNEL SESSION EXISTS');
END IF;
END;
CONNECT RESET;

When I try to create it I get an error stating that
CHANNELSESSION.CHANNELSESSIONID is not valid in the context being
used.

Hopefully you or someone can shed some light on how I can go about
accomplishing this:

Before a row is inserted into AIMCONNECTION, I want to check the
parent row in the CHANNELSESSION table (foreign key is
CHANNELSESSIONID). If the value of ACTIVESESSION is 'N', then raise an
error and not insert the new row. If the value is 'Y', then go ahead
and insert the row.

Is my syntax totally off? Or do I need to use an INSTEAD OF trigger
instead?

Thanks again for any assistance.


CREATE TRIGGER AIMP.VALIDATE NO CASCADE BEFORE INSERT ON
AIMD.AIMCONNECTION REFERENCING NEW AS NEWROWS FOR EACH ROW MODE
DB2SQL WHEN ('N' = (SELECT S.ACTIVESESSION
FROM CHANNELSESSION AS S
WHERE NEWROWS.CHANNELSESSIONID
= S.CHANNELSESSIONID))

SIGNAL SQLSTATE '70001' ('NO VALID CHANNEL SESSION EXISTS');

Note that this trigger assumes that CHANNELSESSIONID exists in the first
place. You need a tad more tweaking to raise the signal if it doesn't
exist or is 'N'.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6

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

Similar topics

5
by: Doug Baroter | last post by:
Hi, DDL: -- create table #task (taskID int identity(1,1) primary key, taskName varchar(25) unique, taskCompleteDate dateTime, taskComplete bit default(0)); /* Business Rules: a) if...
2
by: Doug Baroter | last post by:
Hi, DDLs and DMLs: create table #job (jobID int identity(1,1) primary key, jobName varchar(25) unique not null, jobEndDate dateTime, jobComplete bit default(0), check (( is null and = 0) OR (...
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...
0
by: Fabre Lambeau | last post by:
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): ...
9
by: Edmund Dengler | last post by:
Greetings! Just trying some tests out, and wanted to know about some optimizations. If I do a CHECK constraint on a table, is this used to optimize a SELECT or does Postgresql rely mostly on...
3
by: ferg | last post by:
I have a Customer table. The table has two different CHECK constraints. Then there is the Customer details dialog, which provides the user with an UI for changing users. I have some UPDATE sql,...
1
by: huyuhui | last post by:
The following is a question of LOAD utility. Question: How does the DB2 enforce table check constraints for data added to table with the LOAD utility? A. With the BUILD phase of LOAD B. With the...
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...
3
by: Helen Wheels | last post by:
Can we use parentheses in a check constraint in MS-SQL-server DDL? e.g. I'm having a problem with the following statement: ALTER TABLE . ADD CONSTRAINT CHECK (( IS NULL AND IS NULL) OR (...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
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...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
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...

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.