--
-- Data Integrity for Restricted Count 
--     Table DDL
--     Derek Asirvadem 120131
--     (Very similar code Tested on Sybase ASE 15.0.2)
--

CREATE TABLE Solution (
    ProblemId     ProblemId   NOT NULL,
    SolverId      PersonId    NOT NULL,
    CONSTRAINT UC_PK
        PRIMARY KEY CLUSTERED (ProblemId, SolverId),
    CONSTRAINT SolverCount_ck 
        CHECK ( dbo.SolverCount_fn ( ProblemId ) < 5 ),
    CONSTRAINT Product_ProdCD_fk 
        FOREIGN KEY (ProblemId) REFERENCES Problem(ProblemId),
    CONSTRAINT Product_ProdCD_fk 
        FOREIGN KEY (SolverId) REFERENCES Person(PersonId)
    )
go

CREATE FUNCTION SolverCount_fn (
--
-- Function: Return COUNT(Solver) for Problem
--
        @ProblemId      ProblemId
        )
    RETURNS TINYINT
AS
    RETURN ( SELECT COUNT(SolverId)
        FROM  Solution
        WHERE ProblemId = @ProblemId
        )
go
