SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to get exact values for a constraint in SQL Server??


How to get exact values for a constraint in SQL Server??

Author
Message
yghaziza
yghaziza
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 9
Is there a way to get the exact values for a constraint on a column? I need to display these values in a list of options to choose from in a webpage, but do not want to parse the expression as the result of the following query:

select sys.check_constraints.definition
from sys.check_constraints
inner join sys.columns on sys.check_constraints.parent_object_id = sys.columns.object_id
inner join sys.tables on sys.check_constraints.parent_object_id = sys.tables.object_id
where sys.tables.name = 'myTable'
and sys.columns.name = 'myColumn'
and sys.columns.column_id = sys.check_constraints.parent_column_id

Parsing the expression will be error-prone and very cumbersome. There must be a way to get the exact values of '1D', '2D', '3D', '4D', for example when constraint is created using these values:
alter table myTable add constraint CK_myColumn_Values
check (myColumn in ('1D', '2D', '3D', '4D'))

thanks for the help.
SrcName
SrcName
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 394
i don't see need to list definition of constraint in this case.
Restrict input in web application with validation expression,
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17111 Visits: 6431
The parsing isn't so bad if you use a pattern splitter function and all of your constrains are like this IN type.


WITH CheckConstraint ([definition]) AS (
select 'myColumn in (''1D'', ''2D'', ''3D'', ''4D''))'
),
PatternSplit AS
(
SELECT [definition], itemnumber, item, [matched]
FROM CheckConstraint
CROSS APPLY dbo.PatternSplitCM([definition], '['']')
)
SELECT b.Item
FROM PatternSplit a
INNER JOIN PatternSplit b ON a.ItemNumber + 1 = b.ItemNumber
WHERE a.Item='''' AND b.Item LIKE '%[a-zA-Z0-9]%';




Refer to the 4th link in my signature for the PatternSplitCM FUNCTION code.


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
yghaziza
yghaziza
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 9
@Grasshopper, Can you be more specific on 'restrict with validation expression'? What else is there other than parsing the expression, which is the result of this definition coming from SQL query??
SrcName
SrcName
Old Hand
Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)Old Hand (318 reputation)

Group: General Forum Members
Points: 318 Visits: 394
yghaziza (9/23/2013)
@Grasshopper, Can you be more specific on 'restrict with validation expression'? What else is there other than parsing the expression, which is the result of this definition coming from SQL query??


your goal is to validate some input value in web app.
And we say, our user may insert only value which i define (in this case 1D, 2D ...)
Well,
with restrict with validation expression i mean: create and define regular expression depending on your specification in your web page, if i enter for example 9A then show error on web page.
OR
every allowed value save in table, iniFile, global variable e.t.c and check entered value with event on control where input happens
yghaziza
yghaziza
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 9
@Grasshopper, problem is that client (GUI) has no control over the constraints or values for data as this is read-only GUI. Data is controlled by other means and user can only get the list from dB. And it seems the way to go is either parse the expression when quering constraints, or put those constraints in a separate table and get the values directly.

@SSCrazy, the question is how to get those values you have listed in the first select statement: select 'myColumn in (''1D'', ''2D'', ''3D'', ''4D''))'. I'm not sure how to implement your suggestion Unsure
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17111 Visits: 6431
yghaziza (9/23/2013)
@Grasshopper, problem is that client (GUI) has no control over the constraints or values for data as this is read-only GUI. Data is controlled by other means and user can only get the list from dB. And it seems the way to go is either parse the expression when quering constraints, or put those constraints in a separate table and get the values directly.

@SSCrazy, the question is how to get those values you have listed in the first select statement: select 'myColumn in (''1D'', ''2D'', ''3D'', ''4D''))'. I'm not sure how to implement your suggestion Unsure


A pretty simple modification actually:


WITH CheckConstraint ([definition]) AS (
select 'myColumn in (''1D'', ''2D'', ''3D'', ''4D''))'
),
PatternSplit AS
(
SELECT [definition], itemnumber, item, [matched]
FROM CheckConstraint
CROSS APPLY dbo.PatternSplitCM([definition], '['']')
)
SELECT myColumn FROM myTable WHERE myColumn IN
(
SELECT b.Item
FROM PatternSplit a
INNER JOIN PatternSplit b ON a.ItemNumber + 1 = b.ItemNumber
WHERE a.Item='''' AND b.Item LIKE '%[a-zA-Z0-9]%'
);




Assuming that you're referring to me as SSCrazy (handle is actually Dwain.C). :-)


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
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