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

Question

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

aeri_q

Mr or Mrs. 500

Points: 553
More actions
October 26, 2013 at 5:04 am

#279307

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.

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

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

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

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;-)

aeri_q Mr or Mrs. 500 Points: 553 More actions · Answer 2

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

can u plz explain ???

Sean Pearce SSCoach Points: 15999 More actions · Answer 3

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]

aeri_q Mr or Mrs. 500 Points: 553 More actions · Answer 4

aeri_q

Mr or Mrs. 500

Points: 553

October 28, 2013 at 8:51 am

#1662010

Thanks alot for your help.