Accept only N numbers of same Id in a table

Question

I have the following tables:

Table 1

IDProduct | Price
--------- | -----
1         | $500
2         | $100

Table 2

IDProduct | Desc
--------- | ------
1         | Desc 1
1         | Desc 2
1         | Desc 3

What I want to do is only accept a total of 3 register with the same Id in Table 2, like the above example, if I try to register another row with Id 1 an error must happen or something, is this possible to do in Sql Server or I need to handle this outside sql (js, php, c#, etc.)

In other simple words I want that my Table 1 only accepts a total of 3 rows with the same IdProduct, maybe I have to try it with a trigger? an stored procedure with if, else?

Use a trigger, according to [this SO question](http://stackoverflow.com/questions/398747/can-you-limit-the-number-of-rows-in-a-database-table). — Tim Biegeleisen, Jan 13 '17 at 07:38
It can be done [without triggers](http://stackoverflow.com/q/23510713/15498), but I'd still not recommend that technique. — Damien_The_Unbeliever, Jan 13 '17 at 07:43
I would also say this should be done in business layer of the application. — CiucaS, Jan 13 '17 at 08:00

score 0 · Answer 1 · answered Jan 13 '17 at 07:45

0

In SQL Server Stored procedure, you can simply achieve this:

CREATE PROCEDURE [dbo].[proc_InsertProduct]
(   @ProductId   INT, @Price DECIMAL (9,2)   ) 
AS
BEGIN

    DECLARE @ProductCount AS INT = 0
    SELECT @ProductCount = COUNT(*) FROM Table2 WHERE IDProduct = @ProductId   

   IF @ProductCount < 3
       INSERT INTO Table1 (IDProduct, Price) VALUES (@ProductId, @Price)
   ELSE
      -- You can RAISEERROR or some other way to notify the insert is not happen

END

answered Jan 13 '17 at 07:45

Arulkumar

12,966
14
47
68

This *may* work but you'd want to put better protection around it (explicit transaction, exclusive lock during select) if there's any possibility that you're building a system that has to support more than a single user at a time. Otherwise there's an obvious race here where two sessions perform a count, both observe the count is less than 3, then both insert and take the count over 3. – Damien_The_Unbeliever Jan 13 '17 at 08:14

Abdul Rasheed · Answer 2 · 2017-01-13T09:02:13.030

0

Using a CHECK CONSTRAINT with a User Defined Function can solve this.

First create UDF dbo.CheckIDProduct to get the count of a IDProduct from Table_2

After that add a CHECK CONSTRAINT on Table_2

ALTER TABLE Table_2
  ADD CONSTRAINT chkIDProductCount
  CHECK (dbo.CheckIDProduct(IDProduct) <= 2);

edited Jan 13 '17 at 09:02

answered Jan 13 '17 at 08:45

Abdul Rasheed

6,486
4
32
48

Accept only N numbers of same Id in a table

2 Answers2