The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Enforce specific number of child rows (SQL 2005)

I have a parent table where each entity can have only 4 children rows. How can this be enforced? At first I thought the parent could have columns Child1_Id, Child2_Id, etc. but it would be hard to query for all children which will be commonly required.

Tuesday, July 01, 2008
Use a trigger?
Jason Send private email
Tuesday, July 01, 2008
You should be able to do this with a trigger.

Here's a good page to read about triggers in SQL Server:
Steve McLeod Send private email
Tuesday, July 01, 2008
Can you describe why there can only be four child rows?
David Aldridge Send private email
Tuesday, July 01, 2008
You can also do this with a scalar function that returns the count of records and a constraint that looks like: (dbo.fn_mycount([ID])<=4).  The error message tends to be somewhat unhelpful though so make sure the constraint name is descriptive.

The constraint will fire for updates as well as inserts.

A trigger can be limited to just inserts and allows you to raise a useful error message which explains what's wrong.
DefectiveMonk Send private email
Wednesday, July 02, 2008
Do you mean exactly four, or a maximum of four?

Do each of the children play the same role or do they play distinct roles?  An example of distinct roles would be "primary insurer" and "secondary insurer"  for two roles.

In the case of an automobile with four wheels,  the four tires play the same role in some ways  (the tires can be rotated) but different roles in other ways (each tire tells a different story after an accident).
Walter Mitty Send private email
Wednesday, July 02, 2008
One solution would be to create 4 rows in the child table upon creation of the parent.  Have one column in the child table indicating whether it is active or not.

A trigger can work.  At first glance it looks like it might take more work to set up, but without knowing more about what you're doing, I can't really guess.

Having 4 columns in the parent isn't strictly impossible.  You could just union 4 queries (select child1 from parent union select child2 from parent...etc...)

I'd probably go with the first solution just because it would be easy to query:
select * from children where parent = 5  and is_active=1;

It would be easy to see how many child slots are available to be inserted:
select * from children where parent = 5 and is_active=0;

If you need exactly four (compared to maximum 4) records, then I wouldn't even consider the trigger.
Lance Hampton Send private email
Wednesday, July 02, 2008
If you need exactly four, you can pre-create the rows, and then remove delete & insert permission for the table.  That way DB users are only able to do updates.

A little draconian...
Wednesday, July 02, 2008
I would normalize and use a trigger or constraint, but you can also use 2005's new PIVOT/UNPIVOT capabilities if you do design all four columns in the same row - so the query to get all the children (in rows) might not be as onerous as you think.
Cade Roux Send private email
Wednesday, July 02, 2008
hehe, business rules in database triggers. Bad choice. Maybe works for simple stuff, but anything a bit more complex is a nightmare.
Saturday, July 05, 2008
Is this a MAXIMUM child record condition or is this a STRICTLY 4 child record condition.

It depends on what process(es) insert and update the data. If you are talking about an existing system that has uses embeded sql statements or procedures that are not reasonable to change, I'd go with a trigger on Insert checking for a MAX row count condition for the parent.

Creating the child records on creation of the parent is a bit awkward, but it can be done.

Doing things like this in triggers is asking for trouble. Use if you have NO OTHER CHOICE.

If I were designing a new application, I would prefer to manage this logic inside a stored procedure. Post updated datasets as an XML doc to your sprocs, read them with OPENXML as result sets. There you are free to validate, count, do whatever you need. On Create actions, raise error if there are not four supplied. On Update, raise a similar error if fewer than 4 children are supplied - if that is a rule.

This is pretty easy to do in .NET, but not native for many other dev tools. Also makes changes much easier, and keeps the number of stored proc parameters down to a sane level.

Some may not like it, but I've seen it used with tremendous success.
Jason T
Tuesday, July 08, 2008
Use stored procedures ONLY to manage the table.
The sp can manage error conditions (such as COUNT > 4 or COUNT != 4) quite easily, such as in the following example (assume table name is tb4 with fields id identity pk, code nchar(5) - dummy example):

-- assume constraint is to have <=4 rows
-- return 0 on ok, < 0 on errors, > 0 on warnings
-- establish a disjunct error range for each of the operations
create procedure sp_manage_tb4
    @opcode nchar(1) -- I=insert,U=update,D=delete
  , @id int          -- or null for insert
  , @code nchar(5)  -- code to be I/U, null on D
  if @opcode not in ('I','U','D')
    return -1 -- invalid op
  if @opcode = 'I' -- insert
    declare @result int
    set @result = 0 -- ok
    /* lock table (sp_getapplock, select ... with updlock...) */
    if (select count(*) from tb4) > 4
      -- insert results in > 4 rows
      set @result = -2
      -- check insert correctitude here...
      insert into tb4 (code) values (@code)
    -- unlock table
    return @result

  -- handle Delete, return 0 on ok on < 0 on errors
  -- handle Update, return 0 on ok on < 0 on errors

  return 1 -- success with info
Cristian Amarie Send private email
Tuesday, July 15, 2008

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz