Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
aeri_q
aeri_q
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 68
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.
Bhuvnesh
Bhuvnesh
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2924 Visits: 4076
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
aeri_q
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 68
ok .. but how can i stop the insertion in instead of trigger if condition is not matched.
can u plz explain ???
Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 3432
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

@SeanPearceSQL

About Me
aeri_q
aeri_q
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 68
Thanks alot for your help.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search