1

Say I have two tables, Parent and Child. Parent has a MaxChildren (int) field and Child has an Enabled (bit) field and a ParentID (int) field linking back to the parent record.

I'd like to have a constraint such that there can't be more than MaxChildren records for each parent where Enabled = 1. This would mean that any attempt to insert or update any record in the Child table will fail if it goes over the applicable MaxChildren value, or any attempt to lower MaxChildren to below the current number of applicable Child records will fail.

I'm using MS SQL Server, but I'm hoping there's a standard SQL way.

billpg
  • 3,195
  • 3
  • 30
  • 57

1 Answers1

4

This is Standard SQL-92 entry level syntax i.e. uses 'vanilla' syntax such as foreign keys and row level CHECK constraints that are widely implemented in SQL products (though notably not mySQL):

CREATE TABLE Parent
(
 ParentID INTEGER NOT NULL, 
 MaxChildren INTEGER NOT NULL
    CHECK (MaxChildren > 0), 
 UNIQUE (ParentID),
 UNIQUE (ParentID, MaxChildren)
);

CREATE TABLE Child
(
 ParentID INTEGER NOT NULL, 
 MaxChildren INTEGER NOT NULL, 
 FOREIGN KEY (ParentID, MaxChildren)
    REFERENCES Parent (ParentID, MaxChildren)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
 OccurrenceNumber INTEGER NOT NULL, 
 CHECK (OccurrenceNumber BETWEEN 1 AND MaxChildren), 
 UNIQUE (ParentID, OccurrenceNumber)
);

I suggest you avoid using bit flag columns. Rather, you could have a second table without the restriction on MaxChildren then imply the Enabled column based on which table a row appears in. You'd probably want three tables to model this: a supertype table for all children with a subtype tables for Enabled. You could then create a VIEW to UNION the two subtypes with an implied Enabled column e.g. 

CREATE TABLE Parents
(
 ParentID INTEGER NOT NULL, 
 MaxChildren INTEGER NOT NULL
    CHECK (MaxChildren > 0), 
 UNIQUE (ParentID),
 UNIQUE (ParentID, MaxChildren)
);

CREATE TABLE Children
(
 ChildID INTEGER NOT NULL, 
 ParentID INTEGER NOT NULL, 
 MaxChildren INTEGER NOT NULL, 
 FOREIGN KEY (ParentID, MaxChildren)
    REFERENCES Parents (ParentID, MaxChildren)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
 UNIQUE (ChildID), 
 UNIQUE (ChildID, MaxChildren),  
);

CREATE TABLE EnabledChildren
(
 ChildID INTEGER NOT NULL, 
 MaxChildren INTEGER NOT NULL, 
 FOREIGN KEY (ChildID, MaxChildren)
    REFERENCES Children (ChildID, MaxChildren)
    ON DELETE CASCADE
    ON UPDATE CASCADE, 
 OccurrenceNumber INTEGER NOT NULL, 
 CHECK (OccurrenceNumber BETWEEN 1 AND MaxChildren), 
 UNIQUE (ChildID)
);

CREATE VIEW AllChildren
AS
SELECT ChildID, 1 AS ENABLED
  FROM EnabledChildren
UNION
SELECT ChildID, 0 AS ENABLED
  FROM Children
EXCEPT
SELECT ChildID, 0 AS ENABLED
  FROM EnabledChildren;
onedaywhen
  • 55,269
  • 12
  • 100
  • 138
  • I like the 'OccurrenceNumber' idea, so instead of Enabled, Occurence would be NULL or NOT NULL. Before updating Occurrence to a not-null value (enabling), the code would have to select an unused Occurence value first. Thank you. – billpg Nov 25 '11 at 12:47
  • I like this, but how would you change the `MaxChildren` value when necessary? – Andriy M Nov 25 '11 at 13:28
  • Follow-up question: http://stackoverflow.com/questions/8270377/how-to-update-an-sql-table-with-a-unique-number-within-a-range – billpg Nov 25 '11 at 14:18
  • @AndriyM: just change the value in the Parents table and the value should `CASCADE` to the referencing tables... unless the number of existing children exceed the new maximum, in which case logic would have to be applied to choose which children to delete, which I think would require procedural code e.g. trigger or perhaps stored proc. – onedaywhen Nov 25 '11 at 14:23
  • @billpg: I would recommend against nullable columns. I have a strong preference for the subtype table approach (my second code example). – onedaywhen Nov 25 '11 at 14:24
  • I missed the `ON UPDATE CASCADE` bit entirely! This is all the more silly since I did look for it and couldn't see. I'm so sorry. As for the additional logic for picking excess children to remove, I don't think that is necessary, as per this part of the original post: *‘[…] any attempt to lower MaxChildren to below the current number of applicable Child records will fail. […]’* So, your present solution seems to answer the question, actually. – Andriy M Nov 25 '11 at 15:44
  • Ah, no, sorry again. There is a possibility that an attempt to lower the MaxChildren value might result in an erroneous failure with your suggestion. It is when there are only children with OccurrenceNumbers like 2 and 3, and the present MaxChildren value is 3 and you are attempting to lower it to 2. The constraints would block it even though the actual number of children is 2. There somehow needs to be a renumeration process, but that would probably complicate very much this otherwise elegant solution. – Andriy M Nov 25 '11 at 15:51
  • @AndriyM: obviously, the user needs to ensure the data fits the constraints if they want the update to succeed ;) I envisage some 'helper' CRUD procs that would do the 'renumeration' on behalf of the user. – onedaywhen Nov 28 '11 at 08:53