--------------------------------------------------------------------------------
--
--   Copyright (c) 2009 Software Gems Pty Ltd
--   Derek Asirvadem  04 Dec 09
--
--------------------------------------------------------------------------------
-- Correct method for Referential Integrity for Exclusive Subtypes.
--    Function code
--------------------------------------------------------------------------------
-- Note
--   - Variations of this can be used to enforce permitted combinations of
--     Non-exclusive Subtypes.
--
--   - 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:
--   DDL: http://www.softwaregems.com.au/Documents/Tutorial/Subtype%20CHECK.sql
--   Discussion: http://stackoverflow.com/questions/4896831/how-to-implement-referential-integrity-in-subtypes
--------------------------------------------------------------------------------

--------------
-- Function --
--------------

DROP FUNCTION ValidateExclusive_fn
GO

CREATE FUNCTION ValidateExclusive_fn (
        @ProductId      ProductId,  -- Subtype/Parent PK
        @ProductType    CHAR(1)     -- Subtype Discriminator
        )
    RETURNS TINYINT
AS

    -- Validate correct Basetype (ProductId & ProductType) Exists
    -- for the given Subtype.  The Product Primary Key (ProductId)
    -- prevents more than one Basetype; in combination with the
    -- Check Constraint that calls this Function (for each Subtype),
    -- it prevents every Subtype except the given one.  Eg. For a
    -- given ProductId, if a ProductType "B" exists, the insertion of
    -- a ProductType "C" is prevented.

    RETURN (
        SELECT COALESCE(1, 0)       -- Exists=1
                                    -- Not Exists=Null, 0 substituted
            FROM  Product
            WHERE ProductId   = @ProductId
            AND   ProductType = @ProductType
        )
GO

--------------------------------------------------------------------------------
    -- 09 Feb 13
    --
    -- I have been informed that the above SELECT looks 'weird' to some people,
    -- and begs a question.  Such people should note that it is contraction of:
    --
    -- SELECT COALESCE( (
    --     SELECT 1                 -- Exists=1; Not Exists=Null
    --        FROM  Product
    --        WHERE ProductId   = @ProductId
    --        AND   ProductType = @ProductType
    --        )
    --     , 0 )                    -- Substitute 0 for Null
--------------------------------------------------------------------------------
