--------------------------------------------------------------------------------
--
--   Copyright (c) 2009 Software Gems Pty Ltd
--   Derek Asirvadem  06 Jan 09
--
--------------------------------------------------------------------------------
-- Limit Count of Child Rows
--    Related DDL code
--------------------------------------------------------------------------------
-- Note
--   - Declarative Referential Integrity is used throughout
--   - All Constraints are implemented Declaratively
--   - Using SQL-92
--   - Tested on Sybase ASE 15.0.2
--
--   - The CHECK Constraint in its vanilla form allows the inserted row (only)
--     to be checked
--   - When the inserted row needs to be CHECKed against other rows (in either
--     the same table, or another table), that code is placed in a Function,
--     which is called by the CHECK Constraint
--   - Although SQL is supposed to allow a subquery in the CHECK, not all
--     platforms provide it.
--------------------------------------------------------------------------------
-- Related:
--   Function: http://www.softwaregems.com.au/Documents/Tutorial/Child_Count_fn.sql
--------------------------------------------------------------------------------

--------------------------
-- Generic Parent Table --
--------------------------

CREATE TABLE Parent (
    ParentKey    CHAR(4)   DEFAULT CHAR(0)  NOT NULL,
    Description  CHAR(32)  DEFAULT CHAR(0)  NOT NULL,
    CONSTRAINT UC_PK
        PRIMARY KEY CLUSTERED ( ParentKey )
    )
GO

-------------------------
-- Generic Child Table --
-------------------------

CREATE TABLE Child (
    ParentKey    CHAR(4)   DEFAULT CHAR(0)  NOT NULL,	-- First component of Child Key
    ChildNo      SMALLINT  DEFAULT 0        NOT NULL,	-- Second component of Child Key
    Description  CHAR(32)  DEFAULT CHAR(0)  NOT NULL,
    CONSTRAINT UC_PK
        PRIMARY KEY CLUSTERED ( ParentKey, ChildNo ),
    CONSTRAINT Parent_IsParentOf_Child_fk
        FOREIGN KEY ( ParentKey ) REFERENCES Parent( ParentKey )
    )
GO

--------------------------------------
-- Implement Declarative Constraint --
--------------------------------------

ALTER TABLE Child
    DROP CONSTRAINT Child_Max_10_ck
GO
ALTER TABLE Child
    ADD  CONSTRAINT Child_Max_10_ck
        -- Note GT, not GTE, this references the existing rows
        CHECK ( dbo.Child_Count_fn ( ParentKey ) < 10 )
GO

-----------------------
-- Prerequisite Data --
-----------------------

INSERT Parent VALUES ( "AAAA", "Parent One" )
GO

INSERT Child VALUES ( "AAAA",  1, "Child One" )
INSERT Child VALUES ( "AAAA",  2, "Child Two" )
INSERT Child VALUES ( "AAAA",  3, "Child Three" )
INSERT Child VALUES ( "AAAA",  4, "Child Four" )
INSERT Child VALUES ( "AAAA",  5, "Child Five" )
INSERT Child VALUES ( "AAAA",  6, "Child Six" )
INSERT Child VALUES ( "AAAA",  7, "Child Seven" )
INSERT Child VALUES ( "AAAA",  8, "Child Eight" )
INSERT Child VALUES ( "AAAA",  9, "Child None" )
INSERT Child VALUES ( "AAAA", 10, "Child Ten" )  -- 10 Child rows succeeds
GO

SELECT *
    FROM Child
GO
SELECT  *,
        [Children] = dbo.Child_Count_fn ( ParentKey )
    FROM Parent
GO

---------------------------
-- Test: Child_Max_10_ck --
---------------------------

INSERT Child VALUES ( "AAAA", 11, "Child Eleven" )  -- Fails
GO
-- INSERT failed with "Check constraint violation Child_Max_10_ck"

--------------------------------
-- Transaction Considerations --
--------------------------------
--
--------------------------------------------------------------------------------
-- As with all attempts to update the database, in addition to (not, instead
-- of!) the constraint, that implements the various business rules (in this case,
-- limiting the number of children), the client program *and* the transaction
-- object should ensure that illegal updates are not *attempted*.  It is grossly
-- inefficient to attempt prevented operations, as both the partially complete
-- transaction and the subsequent Rollback, can be avoided, and avoided easily.
--------------------------------------------------------------------------------
