[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

How do I ensure that some attributes in my table are not duplicated in an already existing table?

Hello:

I have a table like this:

tblRate (PkRateId, UserId, Rate, Start, End, Active, CreatedDate, ModifiedDate)

Now, I want to create the following rules, but don't know how. Please help:

1st REQUEST:
1. the table should reject entries (at the SQL Server level) when there is already a UserId with overlapping Start and End times.
For example, if the table contained:

1, User24, $20, 10/17/2006, 10/17/2008, y, 9/26/2006, 9/28/2007
2, user25, $25, 10/17/2006, null, y, 9/26/2006, 9/28/2007

I could not enter in a new record:
User24, $24, 10/24/2007, 10/25/2007, n, 9/30/2006, 9/30/2007

Because User24 already has an active rate. I should, however, be able to update where the PkRateId is 1 and change 10/17/2006 to 10/25/2007 if I wanted (or any other field for that matter).  

2nd REQUEST:
Start Date is always before End Date

3rd REQUEST:
EndDate can only be Null for any rate if "Active" is "y"

4th REQUEST:
There can only be one "y" in Active for any one user.

Btw: PkRateId is Primary Key with an identity specification.

THANKS!
0
freezegravity
Asked:
freezegravity
  • 2
  • 2
1 Solution
 
freezegravityAuthor Commented:
"Because User24 already has an active rate. I should, however, be able to update where the PkRateId is 1 and change 10/17/2006 to 10/25/2007 if I wanted (or any other field for that matter).  "

I meant to say ... Because User24 already has an active rate within the time specified by the new entry. I should, however, be able to enter in more than one rate for a user if the timeperiod's are different.

Thanks!
0
 
Scott PletcherSenior DBACommented:
1.  Will require a custom trigger to enforce.

2.  Add constraint to table:
CHECK([StartDate] < [EndDate])

3.  Add constraint to table:
CHECK([EndDate] IS NOT NULL OR ([EndDate] IS NULL AND [Active]= 'Y')

4.  Must be enforced via custom trigger -- could be same trigger as used for #1.
0
 
Scott PletcherSenior DBACommented:
If you would like samples of the trigger, just let me know.
0
 
freezegravityAuthor Commented:
Custom triggers does seem to be what might work.

THANKS!
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now