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

How to get exact values for a constraint in SQL Server?? Expand / Collapse
Author
Message
Posted Sunday, September 22, 2013 11:40 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 23, 2013 5:06 PM
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.
Post #1497267
Posted Monday, September 23, 2013 1:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 10, 2014 2:47 PM
Points: 94, Visits: 330
i don't see need to list definition of constraint in this case.
Restrict input in web application with validation expression,
Post #1497281
Posted Monday, September 23, 2013 2:37 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:30 PM
Points: 3,627, Visits: 5,273
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!
Post #1497293
Posted Monday, September 23, 2013 9:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 23, 2013 5:06 PM
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??
Post #1497443
Posted Monday, September 23, 2013 11:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 10, 2014 2:47 PM
Points: 94, Visits: 330
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
Post #1497479
Posted Monday, September 23, 2013 3:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 23, 2013 5:06 PM
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
Post #1497572
Posted Monday, September 23, 2013 6:17 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:30 PM
Points: 3,627, Visits: 5,273
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


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!
Post #1497600
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse