473,372 Members | 1,352 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,372 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 7144
> 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...
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
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: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
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: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
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...

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.