SQL Clone
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 (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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 ???
Keith Tate
Keith Tate
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1042 Visits: 979
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
aeri_q
aeri_q
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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.
LinksUp
LinksUp
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1039 Visits: 4457
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/
wolfkillj
wolfkillj
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1504 Visits: 2582
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
aeri_q
aeri_q
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 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 Smile
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