September 25, 2011 at 8:22 am
Hi
I have two columns name and Id in a table
I want to have the design in such a way that it should not allow to insert more than 300 unique values.
For example I can insert with Id value as 001 for maximum 300 records
If I insert 301st record with Id value with 001 it should not allow and throw the error
How I can achieve this?
What constraint I should use for this
Thanks
Thanks,
Pandeeswaran
September 25, 2011 at 8:51 am
Right now the only idea I have in an insert/update trigger.
Depending on how often you error out on that 300 limit would make me decide wether to use an instead of or after trigger.
September 25, 2011 at 7:03 pm
pandeesh (9/25/2011)
HiI have two columns name and Id in a table
I want to have the design in such a way that it should not allow to insert more than 300 unique values.
For example I can insert with Id value as 001 for maximum 300 records
If I insert 301st record with Id value with 001 it should not allow and throw the error
How I can achieve this?
What constraint I should use for this
Thanks
Details are in the comments in the code below... no trigger needed. I agree with Remi, though... if you have a LOT of rows to insert, an INSTEAD OF trigger may be better because this method does insert the rows and then jerks them out (think ROLLBACK) if the constraint is violated. That means that if there are other triggers on this table, they might take some action and then their actions will also be rolled back due to the implicit transaction CHECK failure.
--===== Create this function
CREATE FUNCTION dbo.CheckCount
(@ID INT, @MaxCount INT)
RETURNS INT AS
BEGIN
RETURN (SELECT CASE WHEN COUNT(*)<=@MaxCount THEN 1 ELSE 0 END FROM dbo.Test t1 WHERE t1.ID = @ID);
END
;
GO
--===== Create a table that uses the function as a CHECK constraint
CREATE TABLE dbo.Test
(
ID INT CHECK (dbo.CheckCount(ID,300)=1),
Name VARCHAR(100)
)
;
GO
--===== Insert 300 rows for two different ID's to show it can be done
INSERT INTO dbo.Test
(ID,Name)
SELECT TOP 300
ID = 1, Name = NEWID()
FROM sys.all_columns
;
INSERT INTO dbo.Test
(ID,Name)
SELECT TOP 300
ID = 2, Name = NEWID()
FROM sys.all_columns
;
--===== Insert 299 rows for ID 3 for a different demo
INSERT INTO dbo.Test
(ID,Name)
SELECT TOP 299
ID = 3, Name = NEWID()
FROM sys.all_columns
;
--===== Now, try to add just one more row for each ID that has a 300 count.
-- The constraint won't allow either.
INSERT INTO dbo.Test
(ID,Name)
SELECT ID = 1, Name = NEWID()
;
INSERT INTO dbo.Test
(ID,Name)
SELECT ID = 2, Name = NEWID()
;
--===== Try to add 10 rows to the ID that has a 299 count.
-- NONE of the new rows will be inserted because we don't know
-- what the right row is.
INSERT INTO dbo.Test
(ID,Name)
SELECT TOP 10
ID = 3, Name = NEWID()
FROM sys.all_columns
;
--===== Also show that NO rows will be inserted if we try
-- to insert more than 300 on the first insert.
INSERT INTO dbo.Test
(ID,Name)
SELECT TOP 301
ID = 4, Name = NEWID()
FROM sys.all_columns
;
INSERT INTO dbo.Test
(ID,Name)
SELECT TOP 500
ID = 5, Name = NEWID()
FROM sys.all_columns
;
--Jeff Moden
Change is inevitable... Change for the better is not.
September 26, 2011 at 2:05 pm
I probably would have gone with the trigger as well, but that's mainly because I don't like having to go outside the table definition to see what a check constraint is doing.
The Check Constraint probably performs better.
Will the normal process insert one row at a time or will it work in batches?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply