Limiting list of values for a column from another table's column

  • I want to limit the list of values for a column from another table's column with some conditions.

    For that i tried to make RULE and added CHECK constraint on that column with a subquery .

    But unfortunately sql does not allow subqueries neither in CHECK constraints nor in RULES defined on column.

    I am using a stored procedure for insertions in my table, but the procedure is dynamically generated from the application(Desktop Application) , and is used for insertions in many other tables as well . That is why , i cannot handle the necessary checks in the procedure. I want some database level check , which could raise an error if an out of list value is tried to be inserted in the table.

    Here is the create table statement for both of my tables :

    CREATE TABLE RANGE

    (

    SYST varchar(3) NOT NULL,

    ACCNT_TYP varchar(1) NOT NULL,

    NO_RANGE varchar(2) NOT NULL,

    FROM_NO varchar(10) NOT NULL,

    TO_NO varchar(10) NOT NULL,

    CURRENT_NO varchar(10) NOT NULL,

    PRIMARY KEY (SYST, ACCNT_TYP, NO_RANGE)

    )

    CREATE TABLE ASSET_CLASS

    (

    SYST varchar(3) NOT NULL,

    ASSETCL varchar(5) NOT NULL,

    ACCNTDTR varchar(5) NULL,

    NO_RANGE varchar(2) ,

    SCRN_LO varchar(5),

    PRIMARY KEY (SYST, ASSETCL)

    )

    What i want, is a check like this, to be applied on NO_RANGE column of ASSET_CLASS at the time of insertion:

    CHECK (LIST IN (SELECT NO_RANGE FROM RANGE WHERE ACCNT_TYP = 'A')

    thus, No_RANGE in ASSET_CLASS can hold only those NO_RANGES which exist in RANGE table and have an ACCNT_TYP = 'A'.

    I cannot handle this with foreign key constraint because NO_RANGE is a part of composite primary key and in RANGE table , thus i cant make a foreign key on NO_RANGE in ASSET_CLASS referencing a part of primary key from RANGE table.

    Please help me to achieve this .. ??

    If i write a trigger for INSERT ON ASSET_CLASS, i dunt know how to put a check on the value which is tried to be inserted , I mean how will i access the value which is tried to be inserted in NO_RANGE column , to perform the check ..???

    :S I dunt know how to do this... Any help would be greatly appreciated.

  • aeri_q (10/26/2013)


    If i write a trigger for INSERT ON ASSET_CLASS, i dunt know how to put a check on the value which is tried to be inserted , I mean how will i access the value which is tried to be inserted in NO_RANGE column , to perform the check ..???

    Magic table like Inserted and deleted handle these kind if data .

    Try to use "instead of" trigger. it will help you.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • ok .. but how can i stop the insertion in instead of trigger if condition is not matched.

    can u plz explain ???

  • CREATE TRIGGER CHECK_RANGE ON ASSET_CLASS

    FOR INSERT, UPDATE

    AS

    IF NOT EXISTS(SELECT * FROM inserted i JOIN RANGE r ON i.NO_RANGE = r.NO_RANGE WHERE r.ACCNT_TYP = 'A')

    BEGIN

    RAISERROR('Invalid NO_RANGE', 16, -1);

    ROLLBACK;

    END;

    GO

    The SQL Guy @ blogspot[/url]

    @SeanPearceSQL

    About Me[/url]

  • Thanks alot for your help.

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

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