Limit Number of Records In Table Based on Column Value 2

[Auguy]

Is there a way to limit the number of records in a table for a value of one of the columns?
Say I have a column named BoxID.
I want to limit the number of records in this table that have BoxID = 'ABC' to 2 records.
You could think of this as an Invoice Detail table where I don't want there to be more than 2 records with BoxID of 'ABC'
Would something like this work?
Insert into Table ... Where (Select Count(1) From Table Where BoxID = 'ABC' Group By BoxID) < 2
Is there another approach?

Auguy
Sylvania/Toledo Ohio

 

[gmmastros]

Would something like this work?

You could probably get something like that to work, but I wouldn't recommend it.

Basically, you could find everywhere in your code that you insert rows and make sure you don't exceed your limit. You may also want to look at all your code that updates the table because you could have a statement like...

Update table set BoxId = 'ABC' Where BoxId = 'XYZ'

Instead, I would suggest that you write an insert trigger and an update trigger that limits the number of rows in your table. If written correctly, you could accomplish your goal, and you wouldn't need to check all your existing code. Better yet, you wouldn't need to worry about future code either.

Writing triggers can be tricky. I encourage you to give the trigger method a try, but post the code here so that we can take a look and (possibly) offer suggestions. A poorly written trigger can have awful performance impacts, and worse... if it's not done correctly, can cause data integrity problems.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Olaf Doschke]

You can also reverse a relation, eg if BoxID is a foreign key and your limitation of two is quite equal to a person having a mother and father and no further parents, you'd let the person record have a motherID and fatherID instead of father and mother referring to the childID. It does not work out well anyway, as parents can have many children. The general heads up is about defining your relations in the right direction and place. If you don't see a solution to the place of a foreign key in either of the two related tables, you most often need an extra relation table (n:m).

In the same manner as fatherID and motherID, your box may have two such IDs instead of two child records pointing to the same box, so the box points to its two related records and that is simply limited by the number of foreign key fields each box record has. This, of course, means a code refactoring, but may be worth it by being a database model better reflecting the real world situation.

If it does not reflect your situation, indeed, what George last said about triggers is to be taken very serious. I know you come from using Foxpro DBFs, maybe never did DBCs, but they also offer triggers. Unlike SQL Server any VFP DBC trigger is called per record, eg an update of a table causes many trigger events. In SQL Server, in contrast, you reference a virtual table of new or updated or deleted records in your trigger code (depending on the trigger type of course) and it may contain multiple records. So code written to handle a single record is problematic.

Also besides the three base types of insert, update and delete triggers, you can choose between BEFORE, INSTEADOF and AFTER, so triggers can run before, instead of or after the normal SQL operation. As you want to reject wrong data your choice needs to be BEFORE or INSTEAD OF triggers to start with, an AFTER trigger deleting unwanted records would be a weak concept. And as George said, you need one for INSERTS and one for UPDATEs, which also cover browsing data in the management studio and editing it there and MERGE and some more I even don't think about, so not take it too literally.

In case you would only like to allow one value, a unique index would cover that much simpler. Another simple solution would be to have a secondary bit field you'll need to set to 0 or 1, i.e. make it a non-nullable bit field. In conjunction with an index on the field pair of BoxId, bitfield a uniqueness of that would limit the BoxId to two occurrences, too. But you hardly could set the bit field automatic, so that again would mean changing all code.

Bye, Olaf.

 

[gmmastros]

When working with triggers, you need to be very careful. Like Olaf said, you cannot assume that there will only be 1 row inserted or updated at a time. You also need to be careful about performance. If written incorrectly, your trigger could slow your database to a crawl.

That being said, I worked up an example that should do the trick.

use tempdb
GO
Create Table TestLimit(BoxId varchar(20))
GO
Alter Trigger [dbo].[More_than_two_ABC_rows] On [dbo].[TestLimit] 
For Update, Insert
As
Set NoCount On;
SET XACT_ABORT ON;

If Not Exists(Select * From Inserted Where BoxId = 'ABC')
	Return;

if (Select Count(*) From TestLimit Where BoxId = 'ABC') > 2
	Begin
		raiserror('Too many ABC rows', 16, 1)
		rollback transaction
	End
GO

Note that I create the table in the TempDB database. Every time the SQL Server service is restarted, the TempDB table is recreated, so you don't need to worry about polluting your database with test code.

Also note that I I check a table named "Inserted". Within triggers, there will always be a pseudo table named INSERTED and Another named DELETED. Both of these table will have the same structure as the table you write the trigger for (in this case, the TestLimit table).

With an insert trigger, the DELETED table will not have any rows, but the inserted table will have the data that was just inserted.

With an update trigger, the DELETED table will show the data as it appeared before the update, and the INSERTED table will have the updated data.

So... the first thing I do is check the inserted table for BoxId = 'ABC'. If there are no rows matching, then the count of rows would not be affected, so there's really nothing to do.

If an ABC row is updated or inserted, then the trigger checks the count. If the count exceeds 2, an error is raised and a rollback is issued. The rollback will effectively cancel the operation that caused the trigger to fire in the first place.

To test the trigger....

Delete From TestLimit

Insert Into TestLimit(BoxId) Values('ABC') -- this works
Insert Into TestLimit(BoxId) Values('ABC') -- this works

Insert Into TestLimit(BoxId) Values('ABC') -- this fails

-- Note that there are only 2 ABC rows.

Insert Into TestLimit(BoxId) Values('xyz') -- this works
Insert Into TestLimit(BoxId) Values('xyz') -- this works
Insert Into TestLimit(BoxId) Values('xyz') -- this works
Insert Into TestLimit(BoxId) Values('xyz') -- this works

Delete From TestLimit

Insert
Into	TestLimit(BoxId)
Select	'ABC'
Union All
Select	'ABC'
Union All
Select	'ABC'

Note that this attempts to insert 3 ABC rows with 1 query. The trigger will only fire once, and it will fail. After the query, there will not be any ABC rows in the table.

Delete From TestLimit

Insert Into TestLimit(BoxId) Values('xyz') -- this works
Insert Into TestLimit(BoxId) Values('xyz') -- this works
Insert Into TestLimit(BoxId) Values('xyz') -- this works
Insert Into TestLimit(BoxId) Values('xyz') -- this works

Update TestLimit Set BoxId = 'ABC' Where BoxId = 'xyz' -- this fails

Note that there are 4 xyz rows (which is acceptable). The update tries to change the xyz rows to ABC rows, but the update would end up with 4 ABC rows, which is not acceptable, so the entire operation fails.

In my opinion, the most important part of your job is to make sure the data in the database is as accurate as possible. Triggers are another tool you can use to make sure your data is accurate.

I caution you... do not over-use triggers. The problem with triggers is that they can slow down your database in unexpected ways. The other problem with triggers is that developers tend to forget about them. This is why it's important to write good error messages for your triggers. If a trigger throws an error, you'll want to know why, and that it was caused by a trigger. You're future you will thank you.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Auguy]

Wow, thanks to both of you. I have some studying to do.

Auguy
Sylvania/Toledo Ohio