Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Limiting list of values for a column from another table's column Expand / Collapse
Author
Message
Posted Friday, October 25, 2013 7:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:23 AM
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.
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 ???

Post #1508448
Posted Friday, October 25, 2013 9:38 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, April 17, 2014 10:48 AM
Points: 534, Visits: 790
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.




Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1508509
Posted Saturday, October 26, 2013 4:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:23 AM
Points: 17, Visits: 68
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.



Post #1508651
Posted Monday, October 28, 2013 9:51 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, April 17, 2014 10:37 PM
Points: 319, Visits: 1,142
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/
Post #1508993
Posted Wednesday, October 30, 2013 1:05 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, April 07, 2014 7:35 AM
Points: 1,172, Visits: 2,413
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
Blog: SQLSouth
Twitter: @SQLSouth
Post #1509919
Posted Thursday, October 31, 2013 11:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:23 AM
Points: 17, Visits: 68
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 :)
Post #1510364
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse