--------------------------------------------------------------------------------
--
--   Copyright (c) 2009 Software Gems Pty Ltd
--   Derek Asirvadem  04 Dec 09
--
--------------------------------------------------------------------------------
-- Correct method for Referential Integrity for Exclusive Subtypes.
--    Related SQL 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:
--   Function: http://www.softwaregems.com.au/Documents/Tutorial/Subtype%20ValidateExclusive_fn.sql
--   Discussion: http://stackoverflow.com/questions/4896831/how-to-implement-referential-integrity-in-subtypes
--------------------------------------------------------------------------------

-----------------------
-- Basetype Product --
-----------------------

CREATE TABLE Product (
    ProductId     ProductId   NOT NULL,
    ProductType   CHAR(1)     NOT NULL,
    Name          CHAR(30)    NOT NULL,
    CONSTRAINT UC_ProductId
        PRIMARY KEY CLUSTERED (ProductId),
    CONSTRAINT ProductType_ck CHECK ( ProductType IN ("B", "C") )
    )
GO

INSERT Product VALUES (99, "B", "Gone with the Wind")
INSERT Product VALUES (88, "C", "Best of Tina Turner")
GO

-------------------------
-- Subtype ProductBook --
-------------------------

CREATE TABLE ProductBook (
    ProductId     ProductId   NOT NULL,
    NumPages      INT         NOT NULL,
    CONSTRAINT UC_ProductId
        PRIMARY KEY CLUSTERED (ProductId),
    CONSTRAINT Product_ProdBook_fk
        FOREIGN KEY (ProductId)
        REFERENCES Product(ProductId)
    )
GO

-----------------------
-- Subtype ProductCD --
-----------------------

CREATE TABLE ProductCD (
    ProductId     ProductId   NOT NULL,
    NumTracks     INT         NOT NULL,
    CONSTRAINT UC_ProductId
        PRIMARY KEY CLUSTERED (ProductId),
    CONSTRAINT Product_ProdCD_fk
        FOREIGN KEY (ProductId)
        REFERENCES Product(ProductId)
    )
GO

-----------------------
-- Exclusive Subtype -- Declarative referential Integrity
-----------------------

ALTER TABLE ProductBook
    ADD CONSTRAINT ProductBook_Excl_ck
        CHECK ( dbo.ValidateExclusive_fn (ProductId, "B") = 1 )

ALTER TABLE ProductCD
    ADD CONSTRAINT ProductCD_Excl_ck
        CHECK ( dbo.ValidateExclusive_fn (ProductId, "C") = 1 )
GO

--------------------------------------------------------------------------------
-- Transaction Considerations -- For the Internet Frauds 04 Feb 13
--------------------------------------------------------------------------------
--
-- Data and Referential Integrity is provided in part via Declarative statements
-- (above).  Attempts such as the following are prevented by DRI:
--   - Insert Product* Subtype without a Product Basetype
--   - Delete a Product Basetype without deleting its Product* Subtype
--
-- The remainder is provided via Transactions.  Transactions that are contained
-- in the database (usually as stored procs, or functions) are Declarations.
-- Attempts such as the following, are prevented by ordinary Transaction
-- implementation:
--   - Insert Product Basetype without a Product* Subtype
--   - Delete a Product* Subtype without deleting the Product Basetype
--
-- This is a tutorial on Subtypes.  It is *not* a tutorial on:
--   - OLTP and implementation requirements
--   - Transactions and Trandaction design
--   - SQL requirements for transactions
--   - Other requirements Related to transactions
-- Do ensure that all the standards and constructs re the above, that have been
-- chosen for your site have been implemented.
--
-- As per common practice, and honest expectations, examples are provided for
-- the subject of the tutorial, only.  The code required for Transactions
-- related to this example are not provided.
--
--------------------------------------------------------------------------------
--
-- Transactional
--
-- Of course, it is asserted that all changes to the database are made
-- via Transactions only, and *not* via direct commands to the table.  The
-- Basetype::Subtype cluster needs to changed via Transactions only.  Example
-- Transactions are:
--
--   ProductBook_Add_tr ( ProductId )       |  ProductBook_Drop_tr ( ProductId )
--     If not exsists ( Select ProductId )  |    If exsists ( Select ProductId )
--       Begin Tran                         |      Begin Tran
--       Insert Product                     |      Delete ProductBook
--         If Error Rollback Tran           |        If Error Rollback Tran
--       Insert ProductBook                 |      Delete Product
--         If Error Rollback Tran           |        If Error Rollback Tran
--       Commit Tran                        |      Commit Tran
--     Else exit                            |    Else exit
--
-- As is typical for examples, simple Inserts and Deletes are provided for
-- verification of the given Basetype::Subtype example and code (ie. not full
-- transactions) are provided here.
--------------------------------------------------------------------------------
-- As with all attempts to update the database, in addition to (not, instead
-- of!) the constraint, that implements the various business rules, the client
-- program *and* the transaction object should ensure that illegal updates are
-- not *attempted*.  Eg. check that basetype:subtype pair does not exist before
-- attempting an insertion.  It is grossly inefficient to attempt prevented
-- operations, as both the partially complete transaction and the subsequent
-- Rollback, can be avoided, and avoided easily.
--------------------------------------------------------------------------------

------------------------------------
-- Exclusive Subtype Verification --
------------------------------------

INSERT ProductBook VALUES (99, 500)
GO
INSERT ProductBook VALUES (99, 999) -- Fail: duplicate Insert
GO
INSERT ProductCD   VALUES (99, 12)  -- Fail: ProductCD_Excl_ck
GO
INSERT ProductCD   VALUES (88, 15)
GO
INSERT ProductBook VALUES (88, 666) -- Fail: ProductBook_Excl_ck
GO

---------------------------
-- Non-exclusive Subtype -- Declarative referential Integrity
---------------------------
--
-- 1. Drop the Discriminator Product.ProductType
-- 2. Exclude the ValidateExclusive_fn CHECK Constraint in the Subtypes
--

ALTER TABLE Product
    DROP ProductType

ALTER TABLE ProductBook
    DROP CONSTRAINT ProductBook_Excl_ck

ALTER TABLE ProductCD
    DROP CONSTRAINT ProductCD_Excl_ck
GO

----------------------------------------
-- Non-exclusive Subtype Verification --
----------------------------------------

INSERT ProductCD   VALUES (88, 15)  -- Fail: duplicate Insert
GO
INSERT ProductBook VALUES (88, 666)
GO
INSERT ProductCD   VALUES (99, 12)
GO
