constraint: enforce unique key on subset of rows
-
I have a table that contains the following 3 fields:
Client_id
Code_id
Isactive_flag
Is it possible to create a constraint where Client_id + Code_id is unique, where Isactive_flag = ‘Y’ ? In other words, I want to enforce a unique key on a subset of the rows in the table.
Thanks
-
What if you've got 2 records with the same Client_id & Code_id but with different Isactive_flag?
Should Server consider this Client with this code active or not?
_____________
Code for TallyGenerator -
The Isactive_flag can only have 2 values: Y or N. If it's N, then it means the record has been "deleted". So I'm only concerned with the records where the Isactive_flag = Y. Thanks
-
If you want to "undelete" "deleted" row you must change status on existing row, not create duplicates.
_____________
Code for TallyGenerator -
My question has to do with preventing duplicate records where the "key" is Client_id plus Code_id, and we're ignoring records where the isactive_flag = 'N'.
Then if you try to "undelete" a record by changing the N to a Y, but another record already exists with the same Client_id and Code_id, you would get an error and the update would not be allowed.
-
Can you explain what exactly do you mean by the last sentence?
If you have "deleted" row with the same Client_id and Code_id as you are trying to insert you must just update status to 'Y' instead of doing insert.
What is not allowed here?
If you reference this table from other(s) by Client_id and Code_id you must avoid duplicates by any means.
If you have status = 'N' then you don't reference this line from anywhere. Right?
So, why not just delete it?
_____________
Code for TallyGenerator -
Sergiy
Forget the Isactive_flag delete undelete stuff. That's confusing and not related to my question. What I want to be able to do is put a unique key constraint over a subset of the records in a file. The criteria for determining the subset could be anything that you could put in a WHERE statement. The key could have one or more fields.
I'm coming from an IBM as/400 (iSeries) background, and on the as/400 you could create a "logical file" (like an index) that could do this. I'm just wondering if there's an equivalent in SQL Server.
Thanks
-
constraints are enforced acrross all data in the table, and there's no conditional constraints.... so you can't have a constraint Client_id and Code_id where isactive_flag = 'N' and a different constraint for isactive_flag = 'Y'
you could put an INSTEAD OF Trigger on the table, so If they try to insert, and the constraint is there, it updates instead. is that what you are trying to accomplish?
I'd probably change the app to check for the existance of the record prior to inserting, but that's up to you.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
just to add to the other replies :
you could use triggers to enforce this check constraint.
Although its better to avoid ....
CREATE TRIGGER mytrigger _IU ON mytable
FOR insert, update
AS
BEGIN
if exists ( SELECT 1 from inserted where Isactive_flag ='y')
begin
if exists (select 1
from inserted
inner join mytable
on inserted.Isactive_flag='y'
and inserted.Client_id = mytable.Client_id
and inserted.Code_id = mytable.Code_id
and inserted.Isactive_flag= mytable.Isactive_flag )
begin
RAISERROR (N'yourerror regarding this constraint', 10, 1);
rollback transaction
end
end
END
I would avoid this kind of check-triggers.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
OK - thanks. I appreciate your help.
-
Rick,
What Sergiy was getting at is that what you want to do is a poor use of a RDMS. Your main purpose here it to prevent duplicate Client_ID and Code_ID rows grouped by whether or not they are active. Attempting to do this in any way, including the warned against trigger, is a bad practice. If you have an existing Client_ID, Code_ID row in your table that has been set to inactive or deleted, you don't add a new 'Active' row in the table. Instead, you find the existing Client_ID, Code_ID where the IsActive_flag is 'N' and you set the flag to 'Y'. Does this make sense?
I'm sure you are probably already learning this, but RDMS's are vastly different from AS/400 systems. What you want to do cannot be done with constraints and as you can see, even if you can accomplish it with a trigger, you have been warned against it from a very knowledgeable and respected poster not to. I would take his advice and come up with a way to accomplish your data needs within the realm of RDMS.
-
John
I appreciate your comments and Sergiy's comments. We've been working over the last 3 years to move our system from an as/400 to SQL Server, and a consulting firm has done all of the work on the new system. They use the "isactive_flag" to logically delete records, so there are never any orphan records. (Is that a "best practice"?) Another practice they have is the primary key of every file is an Identity field (or id). (Is that a best practice too?). So to enforce unique keys for the logical keys, you have to rely on the application. I thought this could be prevented by using something similar to the as/400 logical file, but, alas, it can't. Don't worry - I will take Sergiy's advice and not use the trigger.
The as/400 has some very neat features that SQL Server doesn't, and vice versa. I'm still learning. Change is hard, and I'm still suffering thru this conversion. As Mr. Incredible said, "you'll get over it, with counseling".
Thanks again
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply