November 18, 2010 at 4:13 pm
I have a table called CHTList, which includes two attributes, PatientID and Status.
Can I create a constraint(?) or some other db feature to enforce the following business rule:
I can only have one row where a PatientID has a Status=0 (0 = Scheduled).
PatientID Status
1234 1 (1 = Rescheduled)
1234 1 (1 = Rescheduled)
1234 2 (2 = Canceled)
1234 0 (0 = Scheduled)
I believe I could enforce this rule via a trigger, but I'd rather not use trigger if possible.
Thanks.....
November 18, 2010 at 4:35 pm
You could create a function that would perform a count on the table for rows with Status=0 and add this as constraint to your table.
Another option would be a INSTEAD OF INSERT, UPDATE trigger that would check if the insert or update would be allowed.
I'd probably go with the check constraint.
November 18, 2010 at 8:17 pm
You could also use IF EXISTS, for example
--Insert some sample data
CREATE TABLE #T(PatID INT, Stat INT)
INSERT INTO #T(PatID, Stat)
SELECT 1234,1 UNION ALL
SELECT 1234,1 UNION ALL
SELECT 1234,2 UNION ALL
SELECT 1234,0 UNION ALL
SELECT 5678,1
-- to verify input for this test
--SELECT * FROM #T
DECLARE @Pid INT --In your T-SQL this is a parameter passed in
SET @Pid = 5678
IF NOT EXISTS (SELECT PatID FROM #T WHERE Patid = @Pid and Stat = 0)
BEGIN
INSERT INTO #T (PatID ,Stat)
VALUES(@Pid,0)
END
ELSE
BEGIN
Print 'oops' --Here you could (should) return an error number
END
--DROP TABLE #T
November 18, 2010 at 11:57 pm
A filtered index would seem ideal for this:
create unique index ix_one_scheduled on t (PatientID, Status)
where status = 0
And only one row per patient with a status of zero will be allowed.
Mike
November 19, 2010 at 12:08 am
Use a filtered unique index:
CREATE TABLE dbo.Appointments
(
patient_id INTEGER NOT NULL,
appointment_dt DATETIME NOT NULL,
appointment_status TINYINT NOT NULL
CHECK (appointment_status IN (0,1,2)),
CONSTRAINT [PK dbo.Appointments appointment_dt, patient_id]
PRIMARY KEY CLUSTERED (appointment_dt, patient_id)
);
GO
-- Unique index to enforce the business rule
CREATE UNIQUE NONCLUSTERED INDEX
[UQ dbo.Appointments patient_id, appointment_status ( = 0)]
ON dbo.Appointments (patient_id, appointment_status)
WHERE appointment_status = 0;
GO
-- Various records for patient #1
INSERT dbo.Appointments
(patient_id, appointment_dt, appointment_status)
VALUES (1, '20100801 14:00', 1),
(1, '20100802 13:15', 1),
(1, '20100803 15:30', 1),
(1, '20100804 11:20', 2),
(1, '20100805 09:30', 0);
-- Cannot insert another record for patient #1 with status zero
INSERT dbo.Appointments
(patient_id, appointment_dt, appointment_status)
VALUES (1, '20100806 11:00', 0);
-- Various records for patient #2
INSERT dbo.Appointments
(patient_id, appointment_dt, appointment_status)
VALUES (2, '20100801 14:00', 1),
(2, '20100802 13:15', 1),
(2, '20100803 15:30', 1),
(2, '20100804 11:20', 2),
(2, '20100805 09:30', 0);
November 19, 2010 at 9:47 am
Thank you Lutz, Ron, Mike, and Paul.
It was pretty cool to post a question at the end of day and come in the next day and see this type of response.
I had never heard of the 'filtered unique index' approach. It seemed to be a clean approch, but alas I'm on SQL 2005.
Lutz, creating a function and adding this as a table constraint sounds like a solution I plan on exploring. Unfortunately, it doesn't roll of the tip of my tongue, so to speak. If you can (and care to) expand on it, it would be greatly appreciated.
Thanks to all....
November 19, 2010 at 9:53 am
Hmm, That's a shame.
Two options I can think of:
a) Upgrade - you know it makes sense!
or
b) create a view that contains the patientid and status - but with a where clause such that only status zero are included. Then create a unique index on the view.... I don't have 2005 installed anywhere anymore so unable to test this out I am afraid.
Good luck
Mike
November 19, 2010 at 1:49 pm
mnielsen-1015626 (11/19/2010)
Thank you Lutz, Ron, Mike, and Paul.It was pretty cool to post a question at the end of day and come in the next day and see this type of response.
I had never heard of the 'filtered unique index' approach. It seemed to be a clean approch, but alas I'm on SQL 2005.
Lutz, creating a function and adding this as a table constraint sounds like a solution I plan on exploring. Unfortunately, it doesn't roll of the tip of my tongue, so to speak. If you can (and care to) expand on it, it would be greatly appreciated.
Thanks to all....
First of all: I missed the option for a filtered unique index since I'm still not using SS2K8. A shame to begin with, but I got kinda lucky regarding this thread 😉
Based on the table def Paul provided, here's the code I would use.
As a side note: it will perform a table scan fpr each updated/inserted value, since the clustered index is based on appointment_dt. So it might be worth to test adding a nonclustered index on patient_id,appointment_status.
The big disadvantage of this solution: the function will be called for each and every row you insert or update. This might have a big performance influence depending on your insert/update frequency.
If you already think about moving to SS2K8: this scenario should be added to the "pro" list.
CREATE FUNCTION CheckFnctn(@patient_id int)
RETURNS int
AS
BEGIN
DECLARE @retval int
SELECT @retval = COUNT(*) FROM dbo.Appointments where patient_id=@patient_id and appointment_status=0
RETURN @retval
END;
GO
ALTER TABLE dbo.Appointments
ADD CONSTRAINT chkRowCount CHECK (dbo.CheckFnctn(patient_id) <= 1 );
GO
November 19, 2010 at 5:06 pm
Celko has the right idea, that's a much better design. Do that, if at all possible.
The materialized view Mike suggested is the usual 2005 workaround for the lack of filtered index support:
USE Sandpit
GO
CREATE TABLE dbo.Appointments
(
patient_id INTEGER NOT NULL,
appointment_dt DATETIME NOT NULL,
appointment_status TINYINT NOT NULL
CHECK (appointment_status IN (0,1,2)),
CONSTRAINT [PK dbo.Appointments appointment_dt, patient_id]
PRIMARY KEY CLUSTERED (appointment_dt, patient_id)
);
GO
CREATE VIEW dbo.FilteredIndex
WITH SCHEMABINDING AS
SELECT A.patient_id
FROM dbo.Appointments A
WHERE A.appointment_status = 0
GO
CREATE UNIQUE CLUSTERED INDEX c ON dbo.FilteredIndex (patient_id);
GO
-- Unique index to enforce the business rule
--CREATE UNIQUE NONCLUSTERED INDEX
-- [UQ dbo.Appointments patient_id, appointment_status ( = 0)]
--ON dbo.Appointments (patient_id, appointment_status)
--WHERE appointment_status = 0;
--GO
-- Various records for patient #1
INSERT dbo.Appointments
(patient_id, appointment_dt, appointment_status)
VALUES (1, '20100801 14:00', 1),
(1, '20100802 13:15', 1),
(1, '20100803 15:30', 1),
(1, '20100804 11:20', 2),
(1, '20100805 09:30', 0);
-- Cannot insert another record for patient #1 with status zero
INSERT dbo.Appointments
(patient_id, appointment_dt, appointment_status)
VALUES (1, '20100806 11:00', 0);
-- Various records for patient #2
INSERT dbo.Appointments
(patient_id, appointment_dt, appointment_status)
VALUES (2, '20100801 14:00', 1),
(2, '20100802 13:15', 1),
(2, '20100803 15:30', 1),
(2, '20100804 11:20', 2),
(2, '20100805 09:30', 0);
GO
DROP VIEW dbo.FilteredIndex;
DROP TABLE dbo.Appointments;
Consider posting future questions in the SQL Server 2005 forums, at least until you upgrade 🙂
Please don't use a function (even a slightly more efficient one using EXISTS instead of COUNT). You'll regret it.
November 19, 2010 at 5:16 pm
As written, the function does not work for UPDATE statements because the check constraint will only be evaluated if the column used in the parameter to the function is changed. If we don't modify patient_id, SQL Server does not check the constraint:
USE Sandpit
GO
CREATE TABLE dbo.Appointments
(
patient_id INTEGER NOT NULL,
appointment_dt DATETIME NOT NULL,
appointment_status TINYINT NOT NULL
CHECK (appointment_status IN (0,1,2)),
CONSTRAINT [PK dbo.Appointments appointment_dt, patient_id]
PRIMARY KEY CLUSTERED (appointment_dt, patient_id)
);
GO
CREATE FUNCTION dbo.CheckFnctn(@patient_id int)
RETURNS INTEGER
AS
BEGIN
DECLARE @retval INTEGER
SELECT @retval = COUNT(*) FROM dbo.Appointments where patient_id=@patient_id and appointment_status=0
RETURN @retval;
END;
GO
ALTER TABLE dbo.Appointments
ADD CONSTRAINT chkRowCount
CHECK (dbo.CheckFnctn(patient_id) <= 1 );
GO
--CREATE VIEW dbo.FilteredIndex
--WITH SCHEMABINDING AS
--SELECT A.patient_id
--FROM dbo.Appointments A
--WHERE A.appointment_status = 0
--GO
--CREATE UNIQUE CLUSTERED INDEX c ON dbo.FilteredIndex (patient_id);
--GO
-- Unique index to enforce the business rule
--CREATE UNIQUE NONCLUSTERED INDEX
-- [UQ dbo.Appointments patient_id, appointment_status ( = 0)]
--ON dbo.Appointments (patient_id, appointment_status)
--WHERE appointment_status = 0;
--GO
-- Various records for patient #1
INSERT dbo.Appointments
(patient_id, appointment_dt, appointment_status)
VALUES (1, '20100801 14:00', 1),
(1, '20100802 13:15', 1),
(1, '20100803 15:30', 1),
(1, '20100804 11:20', 2),
(1, '20100805 09:30', 0);
-- Cannot insert another record for patient #1 with status zero
INSERT dbo.Appointments
(patient_id, appointment_dt, appointment_status)
VALUES (1, '20100806 11:00', 0);
-- *************
-- *** OOPS! ***
-- *************
UPDATE dbo.Appointments
SET appointment_status = 0
WHERE patient_id = 1
AND appointment_dt = '2010-08-01 14:00:00.000';
-- Various records for patient #2
INSERT dbo.Appointments
(patient_id, appointment_dt, appointment_status)
VALUES (2, '20100801 14:00', 1),
(2, '20100802 13:15', 1),
(2, '20100803 15:30', 1),
(2, '20100804 11:20', 2),
(2, '20100805 09:30', 0);
GO
--DROP VIEW dbo.FilteredIndex;
DROP TABLE dbo.Appointments;
DROP FUNCTION dbo.CheckFnctn;
Paul
November 19, 2010 at 5:19 pm
Bwahahaha, Celko, you actually DO have a sense of humor!
This poor guy popped into existence without being properly born , committed bigamy and died twice. And you think your life is tough!
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
November 19, 2010 at 6:15 pm
mnielsen-1015626 (11/19/2010)
...I had never heard of the 'filtered unique index' approach. It seemed to be a clean approch, but alas I'm on SQL 2005.....
You should post questions on the forum for the version of SQL you are using becuase people will try to give answers compatible with that version.
November 21, 2010 at 6:18 pm
CELKO (11/19/2010)
The full details are going to be part of the second edition of my TREES & HIERARCHIES book, but the article will get you started.
What is the expected publish-date on that?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply