473,322 Members | 1,846 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.

Check constraint across parent-child tables

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 (([JobEndDate] is null and [JobComplete] = 0) OR
([JobEndDate] is not null and [JobComplete] = 1)));

Q1 with check constraint:
sample dagta
insert into #job (jobName)
values('first job');

transaction A
update #job
set jobEndDate = '12/19/2003', JOBCOMPLETE=1
where jobID = 3;
RESULTSET/STATUS = Success

update #job
set jobEndDate = NULL, JOBCOMPLETE=0
where jobID = 3;
RESULTSET/STATUS = Success

transaction C
update #job
set jobEndDate = '12/19/2003'
where jobID = 3;
RESULTSET/STATUS = Failure

how come check constraint can't set a value which is preset in the
check constraint? If it's the way how it works with MS SQL Server
2000, well, IMHO, it's limiting because the above transaction C is a
valid one. Or maybe check constraint is not fit for this purpose?

Maybe, it doesn't make much sense for me to go into Q2 but I'll try
-- create job's child table, task
create table #task (taskID int identity(1,1) primary key, taskName
varchar(25) unique not null, taskEndDate dateTime, taskComplete bit
default(0), jobID int not null references #job (jobID));
-- skip check constraint for taskEndDate and taskComplete for now

Now, the Business Rule says,
1) if all tasks are complete then automatically set jobComplete for
the #job table to yes;
2) the jobEndDate in the #job table must be >= the last/Max
taskEndDate

I tend to think trigger would slow down data update quite a bit, so,
try to stay away for this purpose if possible.

Always appreciate your idea.
Jul 20 '05 #1
2 7141
> how come check constraint can't set a value which is preset in the
check constraint? If it's the way how it works with MS SQL Server
As I said in my previous answer to a related question from you, the purpose
of a CHECK constraint is to *validate* your data and raise an error if it is
invalid. A CHECK constraint cannot change values in your data. Use a trigger
for that. This is the way CHECK constraints work in standard SQL (although
SQL Server doesn't support full ANSI SQL constraints with subqueries).

Now, the Business Rule says,
1) if all tasks are complete then automatically set jobComplete for
the #job table to yes;


Then the JobComplete column in the Job table looks redundant. Good design
practice is to record a fact in your database once and once only. This
avoids UPDATE anomalies which, as you rightly say, can lead to performance
problems or to incorrect data. You can drop the JobComplete column and use a
view instead:

CREATE VIEW Job_with_completion_status
AS
SELECT J.jobid, J.jobname, J.jobenddate,
MIN(CASE taskcomplete WHEN 1 THEN 1 ELSE 0 END) AS jobcomplete
FROM Job AS J
JOIN Task AS T
ON J.jobid = T.jobid
GROUP BY J.jobid, J.jobname, J.jobenddate
--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #2
> As I said in my previous answer to a related question from you, the purpose
of a CHECK constraint is to *validate* your data and raise an error if it is
invalid. A CHECK constraint cannot change values in your data. Use a trigger
for that. This is the way CHECK constraints work in standard SQL (although
SQL Server doesn't support full ANSI SQL constraints with subqueries). Thanks for the clarification.
Now, the Business Rule says,
1) if all tasks are complete then automatically set jobComplete for
the #job table to yes;
Then the JobComplete column in the Job table looks redundant. Good design
practice is to record a fact in your database once and once only. This
avoids UPDATE anomalies which, as you rightly say, can lead to performance
problems or to incorrect data.

Well, I think you're exactly right from the strictest design
perspective, however, the organization decides to expose all the
columns for some of its applications to a large group of users, and
many of these users would have no idea about the simple logic the fact
that if jobCompleteDate/jobEndDate is NULL it would indicate a job is
not complete but almost all of them would understand perfectly well
when jobComplete is 0 it means the job is not done yet. Hence, slight
design compromise.
You can drop the JobComplete column and use a
view instead:

CREATE VIEW Job_with_completion_status
AS
SELECT J.jobid, J.jobname, J.jobenddate,
MIN(CASE taskcomplete WHEN 1 THEN 1 ELSE 0 END) AS jobcomplete
FROM Job AS J
JOIN Task AS T
ON J.jobid = T.jobid
GROUP BY J.jobid, J.jobname, J.jobenddate

Thanks, probably a UDF can solve the same problem as well?
Jul 20 '05 #3

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

Similar topics

26
by: Agoston Bejo | last post by:
I want to enforce such a constraint on a column that would ensure that the values be all unique, but this wouldn't apply to NULL values. (I.e. there may be more than one NULL value in the column.)...
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...
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): ...
1
by: Bob Stearns | last post by:
When the "new" data IS NOT NULL or whenever the row is stored? In particular is the first clause needed in: ALTER TABLE IS3.FLUSHES ADD CONSTRAINT PALP_LEFT_OV CHECK (PALP_LEFT_OV IS NULL OR...
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...
6
by: pronerd | last post by:
Hi, I am trying to dynamically set an event handler across frames. I have no problems setting properties across frames doing something like parent.ToolMenuFrame.location.href =...
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 (...
2
by: joerg.sailer | last post by:
Hello, is it possible to define a CHECK CONSTRAINT on a table, whereas the CHECK-OPTION will be a SELECT. With this CHECK CONSTRAINT i try to ensure, that there will allways a state only set once...
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...
1
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...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
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
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...

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.