Post reply

Table constraints

  • November 25, 2003 at 8:38 am

    #101772

    Any suggestions on how I can limit the number of rows in a table for a particular criteria but within a table constraint?

    Say I want a user to only enter four rows (don't get into why!) and I'm using their userid as one of the fields how I can I stop them from entering five rows?

    This was a question posed by a student and the answer has to be confined to a table constraint.

    Cheers guys.

  • November 25, 2003 at 10:46 am

    #483587

    Interesting. It would be simple enough to implement with a constraint + a trigger, but the trigger would be enough, so the constraint is not needed.

    Considering what a constraint's range is (one column of a row, or one row in the case of a Table Constraint), I do not believe that this is, in fact, doable with only a constraint. More information than is available to a constraint (i.e. the state of other rows in the table) is needed to implement this.

    My edumicated opinion -- can't be done. Constraints evaluate based only on the row being examined. This will require a trigger.

  • November 25, 2003 at 10:58 am

    #483591

    Use an INSTEAD OF Trigger and if number of rows is greater than the amount specified then do a ROLLBACK TRAN

    Edited by - noeld on 11/25/2003 10:58:49 AM


    * Noel

  • November 25, 2003 at 11:02 am

    #483592

    As there are no table constraints other than PRIMARY KEY and FOREIGN KEY in SQL Server, I presume you mean a column CHECK constraint.

    

    CREATE FUNCTION dbo.CntUser(@userid int)

    RETURNS int AS BEGIN

    RETURN (SELECT COUNT(*)

            FROM TheTable

            WHERE UserId = @userid) END

     

    CREATE TABLE TheTable(

    id int IDENTITY,

    UserId int CHECK (dbo.CntUser(UserId) < 5))

    --Jonathan

    Edited by - Jonathan on 11/25/2003 11:02:24 AM



    --Jonathan

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply