October 25, 2013 at 7:09 am
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.
What i tried to do is :
CREATE RULE asset_cl_rule
AS @list IN(SELECT col3 FROM table2 where col1 = 'A')
i wanted to bind this rule with col2 in table1.
Is there any other way to do this.
I cannot handle this with foreign key constraint because col3 is a part of composite primary key and in table2 , thus i cant make a foreign key in table1 referencing a part of primary key from table2.
Does writing a trigger for table1 at insert would work ??and would it be a good choice ???
October 25, 2013 at 9:38 am
So just to make sure that I understand your question. Before inserting a record into Table1 you want to verify that Table1.Col2 exists in Col3 from Table2 and that Col1 is set to a certain value. Is this correct so far?
Can you create a stored procedure to handle the inserts and do the necessary checks before inserting the values?
If not:
First question is the value in Table2.Col1 also in Table1? Is it static or is it derived by a value in Table1?
Second question is the combination of Col3 and Col1 in Table2 unique and can you create a unique index on the values? If so, you maybe able to create a foreign key on the unique index (depending on the answer to question 1)
An instead of trigger would work in this case, but you will have the administration overhead and performance hit from using the trigger so test it and make sure that it is acceptable.
Also, if you could provide some create table statements along with some sample data that would help. See the article in my signature for details on how to do this.
October 26, 2013 at 4:18 am
Yes you understood well my question.
I am already using a stored procedure to 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 table1's col2.
Here is the create table statement for both of my tables , so that u could understand the stucture:
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'.
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.
October 28, 2013 at 9:51 am
aeri_q (10/26/2013)
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'.
Check this link out http://stackoverflow.com/questions/13000698/sub-queries-in-check-constraint.
I think that pretty much covers what you are trying to do.
Even though the SQL Standard says you can do what you are trying to do, MSSQL says otherwise that this point.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 30, 2013 at 1:05 pm
aeri_q (10/25/2013)
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.
What i tried to do is :
CREATE RULE asset_cl_rule
AS @list IN(SELECT col3 FROM table2 where col1 = 'A')
i wanted to bind this rule with col2 in table1.
Is there any other way to do this.
I cannot handle this with foreign key constraint because col3 is a part of composite primary key and in table2 , thus i cant make a foreign key in table1 referencing a part of primary key from table2.
Does writing a trigger for table1 at insert would work ??and would it be a good choice ???
A foreign key can also reference a UNIQUE constraint - can you add a UNIQUE constraint to table2.col3?
Jason Wolfkill
October 31, 2013 at 11:52 am
Using a function in CHECK constraint solves my problem. Thank you so much for the help and support.
No i cannot add UNIQUE CONSTRAINT on col3 of table2 becox i dont want it to be unique.
But my Problem i solved now. Happy 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply