April 3, 2019 at 6:33 pm
I want to limit the number of rows that I can insert into a table for a certain value to 4. So I can only have 4 instances of a 1, or 4 instances of a 2 etc. in a table. How do I do that with an INSERT statement? Only INSERT into the table if there are 4 or fewer 1's in the table already.
April 3, 2019 at 6:39 pm
You'd have to use an INSERT trigger. It could be an INSTEAD OF INSERT or an AFTER INSERT trigger.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 5, 2019 at 9:35 am
There is another slightly ugly way of doing it (I just hate triggers so I find stupid ways of getting round them)
aside from baking the logic of the count into a proc (by the way the trigger or proc performance will degrade over time as the index seek/scan will be bigger and bigger)
you could use a persisted computed column that lists the duplication value (2 copies of value 99 etc)
but in the caluclation for the column never let it get above 4... then put a unique constraint on the value and the computed column
any insert into the table that violate that constraint will fail and you will get an error
might not be the best way, but its going to be way faster than a trigger
MVDBA
April 5, 2019 at 3:26 pm
I tried the following.
Create a persistent view which only contains the rows with more than four occurences.
Then double that by cross joining it with a table with two rows.
And then put a unique index on it any row in the view is doubled, which is not allowed so this view always stays empty.
(Enforcing the required constraint).
But:
Tried joining 5 rows for the view, but self joins are not allowed for indexed views.
Tried count and select more than 4, but this needs a subquery which is not allowed for indexed views.
Thought about a modulo 4 construction and index that, but there is no 'candidate' for the modulo 4 construction. (Wrong concept).
A count and link this with a table (Join with a table containing numbers over 5). But this needs a subquery.
Maybe somebody can come up with an indexed view, where only the rows which break the constriant shows, within the bounderies which apply to
indexed views. At the moment I do not see a solution within these restrictions.
Does somebody else see a possibility in 'building' such a view (and index on it) ?
Because such a view with an unique index can never contain any rows, such a view does not occupy any data. Only some extra code and the empty view.
Ben
April 5, 2019 at 5:12 pm
That's a very interesting idea. It sounds very complex to implement, but quite nice once you get it working.
The trigger is actually rather easy to write and can be written efficiently. The trigger would need an index with a leading key of the value, but that's easy to create as well (and likely any other method will require that also).
As to the other alternative, I have no idea how MVDBA's idea could be implemented, even in theory. A computed column uses only values from the single row it's on.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 5, 2019 at 10:40 pm
Here a demonstration of the principle. (NOT WORKING).
The idea is if you have 'some' constraint where you can make the offending rows visible, you can use a view to show the offending rows. By adding an unique index to that view (when possible), you can exclude certain situations. For example you have appointment slots for a person in his/her 'private' calendar, you can prevent in the 'general' calendar that the same appointment slots are used. (Making a view where both the appointments private and general are joined and then doubled (Union does not work). The unique index on the view does not allow that to happen).
With the appointments this worked.
I did not succeed in coming up with a view which could be uniquely indexed for the given (four) problem.
----------------------------------------------------------------------------------------------
-- ben brugman
-- 20190405
--
-- Demonstration of working of using an indexed view to implement a constraint.
-- Here the the same number is only allowed to appear 4 times and not five times.
-- The view only contains information about rows appearing at least 5 times.
-- So when the view is empty the rule is not broken.
-- If the view is not empty the rule is broken.
--
-- By putting a unique index on the view the rule can not be broken.
-- Problem, the constructions here are not allowed.
--
----------------------------------------------------------------------------------------------
Create table Max4(number int,
tekst varchar(300))
--
-- Data to demonstrate the effect of the Max4view. With the index on Max4view this view would 'contain' any data.
--
INSERT INTO Max4 VALUES (1, 'Single line')
INSERT INTO Max4 VALUES (2, 'Two lines first')
INSERT INTO Max4 VALUES (2, 'Two lines second')
INSERT INTO Max4 VALUES (3, 'Five lines 1')
INSERT INTO Max4 VALUES (3, 'Five lines 2')
INSERT INTO Max4 VALUES (3, 'Five lines 3')
INSERT INTO Max4 VALUES (3, 'Five lines 4')
INSERT INTO Max4 VALUES (3, 'Five lines 5')
----------------------------------------------------------------------------------------------
--
--
-- Double index trick. (A tally table with two rows).
--
Create Table Index_twice(value int)
Insert Into Index_twice Values (0),(1)
----------------------------------------------------------------------------------------------
--
-- First try, self join the table 5 times. This only gives results if number appears 5 or more times.
--
CREATE VIEW dbo.Max4view
WITH SCHEMABINDING
AS
Select
A.NUMBER,A.tekst Atekst, B.tekst Btekst, C.tekst Ctekst, D.tekst Dtekst, E.tekst Etekst
From dbo.Max4 A
JOIN dbo.Max4 B On a.number = b.number and a.tekst < b.tekst
JOIN dbo.Max4 C On a.number = c.number and b.tekst < c.tekst
JOIN dbo.Max4 D On a.number = d.number and c.tekst < d.tekst
JOIN dbo.Max4 E On a.number = e.number and d.tekst < e.tekst
CROSS JOIN dbo.Index_twice
-- Double up rows if a custom tag matches a stock tag
GO
select * from dbo.Max4view
/*
CREATE UNIQUE CLUSTERED INDEX UX_Max4view
ON Max4view(Number, Atekst);
--
-- Cannot create index on view "Oersoep.dbo.Max4view". The view contains a self join on "Oersoep.dbo.Max4".
--
-- This index would have prevented the insertion of the fith row
--
*/
----------------------------------------------------------------------------------------------
--
-- Second Try. Count the number of rows containing the same value.
-- View only contains the numbers appearing at least 5 times.
--
Go
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'Max4view') DROP VIEW Max4view
GO
CREATE VIEW dbo.Max4view
WITH SCHEMABINDING
AS
WITH
A AS(Select COUNT(NUMBER) over (partition by number) tel, number, tekst From dbo.Max4)
, B AS(SELECT TEL,number,TEKST FROM A WHERE TEL >4)
SELECT number,TEKST FROM B
-- Double up rows if a custom tag matches a stock tag
GO
select * from dbo.Max4view
/*
CREATE UNIQUE CLUSTERED INDEX UX_Max4view
ON Max4view(Number, Atekst);
--
-- Cannot create index on view "Oersoep.dbo.Max4view" because it references common table expression "A"
--
-- This index would have prevented the insertion of the fith row
--
*/
--
-- Cleanup.
--
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'Max4view') DROP VIEW Max4view
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Max4') DROP TABLE Max4
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Index_twice') DROP TABLE Index_twice
----------------------------------------------------------------------------------------------
--
-- Remark there are other situations where this method can be applied.
--
----------------------------------------------------------------------------------------------
April 5, 2019 at 11:10 pm
This was removed by the editor as SPAM
April 6, 2019 at 12:53 pm
Repost. Repost. Repost. Repost. Repost. Repost. Repost. Repost. Repost.
Because the post is not visible (anymore), reposting the same message gives the 'error' that the message has allready been posted.
So Sorry if the script is visible twice.
Her comes the post :
-- Demonstration of the principle (WORKING).
------------------------------------------------------------------------------------------
-- ben brugman
-- 20190406
--
-- Demonstration of using a indexed view to 'enforce' a constraint.
-- In this demonstrations Private timeslots are not allowed to overlap with general timeslots.
--
------------------------------------------------------------------------------------------
Create table PrivateP(ClientId int,
Starttime datetime,
Endtime datetime)
Create table General(ClientId int,
Starttime datetime,
Endtime datetime,
location varchar(60))
--
-- Double index trick. (A tally table with two rows).
--
Create Table Index_twice(value int)
Insert Into Index_twice Values (0),(1)
CREATE VIEW dbo.Overlap
WITH SCHEMABINDING
AS
Select
A.ClientId, A.starttime
From dbo.PrivateP A
JOIN (dbo.General B On A.Endtime > B.Starttime and B.Endtime > A.Starttime) and A.clientid = B.clientid -- Overlap for client
CROSS JOIN dbo.Index_twice -- Double the rows
GO
CREATE UNIQUE CLUSTERED INDEX UX_Overlap
ON Overlap(ClientId);
--
-- The rows in the view are unique this is enforced by the Unique index.
-- Each row in the view appears twice in the view, because of the CROSS JOIN with the Index_twice table.
-- So the view must remain empty to fulfill both conditions.
-- And so enforcing the constraint that there shall be no overlap between PrivateP and General.
--
-- (It does not prevent overlap within the tables).
INSERT INTO GENERAL VALUES(1, '20190405 09:10', '20190405 09:20', 'Room 17')
INSERT INTO GENERAL VALUES(1, '20190405 13:00', '20190405 14:00', 'Room 23')
INSERT INTO PrivateP VALUES(1, '20190405 11:00', '20190405 12:00')
-- THE LINE BELOW (Overlaps with the a private slot).
INSERT INTO GENERAL VALUES(1, '20190405 11:50', '20190405 12:15', 'Room 99')
-- Results in :
-- Cannot insert duplicate key row in object 'dbo.Overlap' with unique index 'UX_Overlap'. The duplicate key value is (1).
------------------------------------------------------------------------------------------
-- Cleanup
DROP VIEW IF EXISTS Overlap
DROP TABLE IF EXISTS General
DROP TABLE IF EXISTS PrivateP
DROP TABLE IF EXISTS Index_twice
------------------------------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply