473,326 Members | 2,147 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,326 software developers and data experts.

Check constraints on clustered columns

balabaster
797 Expert 512MB
Hi guys 'n gals, I'm having an issue wrapping my head around a check constraint that I need to set on a table in my database.

Table: OnCall
Columns: OnCall_PKey (identity), Person_Key, StartDate, EndDate

When a new record is entered, I need a check constraint to make sure that the person entered does not already exist in the table with an overlapping time period:

If in the new record, the start date or the end date fall between the start date and the end date for an existing record having the person key in the new record then the record fails the check.

Example:
One existing data row from my table:
1496, 06/12/2007, 12/12/2007

I try to add:
1496, 09/12/2007, 15/12/2007

The record fails because the new date range overlaps the existing record in the table. No person can have overlapping time periods, however, a person can have multiple time slots in the table, it's just that none of the time slots may overlap.

Any pointers, will be gratefully received.
Dec 6 '07 #1
1 1382
debasisdas
8,127 Expert 4TB
What about defining a trigger on the table ?
Dec 7 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Steve_CA | last post by:
Hi, The more I read, the more confused I'm getting ! (no wonder they say ignorance is bliss) I just got back from the bookstore and was flipping through some SQL Server Administration...
5
by: jim_geissman | last post by:
One table I manage has a clustered index, and it includes some varchar columns. When it is initially created, all the columns in the clustered index are populated, and then some of the longer...
10
by: serge | last post by:
I am doing a little research on Google about this topic and I ran into this thread: ...
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: anonieko | last post by:
A lot of detailed discussion explains the difference between clustered and non-clustered indexes. But very few 'clarifies' why the term used is 'clustered'. Well, once and for all, this is my...
5
by: pb648174 | last post by:
I've been doing a bit of reading and have read in quite a few places that an identity column is a good clustered index and that all or at least most tables should have a clustered index. The tool I...
1
by: Curt | last post by:
What is the difference please?
0
by: negmat | last post by:
Guys, Does SQL Server allow for creating conditional constraints on the table columns? My table is: CREATE TABLE ( IDENTITY (1, 1) NOT NULL ,
1
by: Dan Holmes | last post by:
How do you connect the check constraints on the Table object with the columns in the table? I ended up doing this and it smells bad. foreach (Column cn in t.Columns) { foreach (Check ch in...
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...
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...
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...
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.