4

I have two columns that look like this:

Start_Post  End_Post
----------  --------
102+20.45   153+19.22 
120+21.25   220+25.30
...         ...

And I want to introduce a constraint to each column:

  • Start_Pos
    Min Range: 100+50.30
    Max Range: 150+20.65
  • End_Pos
    Min Range: 150+60.30
    Max Range: 500+20.75

All values are in feet and follow a special format. It's a construction convention and I am not an expert but to my knowledge, 1+00 would be 100 feet. Essentially, taking the above range of 100+50.30 to 150+20.65 would mean a distance of 4970 ft. What I did was just remove the plus sign and subtract the two numbers. Hope that made sense.

Is there a way to define this constraint whilst maintaining the format?

3
  • 2
    Just use decimal type. Store 10050.30 and convert/format appropriately to a string when it is to display the data (assuming that 100+50.30 means 10050 feet and 0.35 of a feet) Jan 26, 2017 at 20:18
  • That is a great idea for a workaround! @ypercube i will keep that in mind unless somebody comes up with a better idea. Appreciate that!
    – SQLserving
    Jan 26, 2017 at 20:21
  • I've attempted to reword your question to make it clearer, also including an explanation from one of your comments. Please take a look if I didn't change the meaning and feel free to edit further if necessary.
    – Andriy M
    Jan 28, 2017 at 21:11

2 Answers 2

8

Assuming the digits to the left of the + are measured in units of 100, you could perhaps create constraints like this:

CREATE TABLE dbo.SurveyData
(
    StartPostStation int NOT NULL
    , StartPostPlus decimal(38,3) NOT NULL
    , EndPostStation int NOT NULL
    , EndPostPlus decimal(38,3) NOT NULL
    , CONSTRAINT StartMin
        CHECK ((StartPostStation * 100 + StartPostPlus) > 100*100+50.3)
    , CONSTRAINT StartMax
        CHECK ((StartPostStation * 100 + StartPostPlus) < 150*100+20.65)
    , CONSTRAINT EndMin
        CHECK ((EndPostStation * 100 + EndPostPlus) > 150*100+60.3)
    , CONSTRAINT EndMax
        CHECK ((EndPostStation * 100 + EndPostPlus) < 500*100+20.75)
);

However, this doesn't allow dynamic constraints, all rows in the table will be constrained to those measurements, which may in fact be exactly what you need.

If you need dynamically adjusting constraints for each independent row, you could do this:

CREATE TABLE dbo.SurveyData
(
    StartPostStation int NOT NULL
    , StartPostPlus decimal(38,3) NOT NULL
    , EndPostStation int NOT NULL
    , EndPostPlus decimal(38,3) NOT NULL
    , CONSTRAINT StartMin
        CHECK ((StartPostStation * PostUnits + StartPostPlus) > MinStartPost * PostUnits + MinStartPostPlus)
    , CONSTRAINT StartMax
        CHECK ((StartPostStation * PostUnits + StartPostPlus) < MaxStartPost * PostUnits + MaxStartPostPlus)
    , CONSTRAINT EndMin
        CHECK ((EndPostStation * PostUnits + EndPostPlus) > MinEndPost * PostUnits + MinEndPostPlus)
    , CONSTRAINT EndMax
        CHECK ((EndPostStation * PostUnits + EndPostPlus) < MaxEndPost * PostUnits + MaxEndPostPlus)
    , MinStartPost int NOT NULL
    , MinStartPostPlus decimal(38,3) NOT NULL
    , MaxStartPost int NOT NULL
    , MaxStartPostPlus decimal(38,3) NOT NULL
    , MinEndPost int NOT NULL
    , MinEndPostPlus decimal(38,3) NOT NULL
    , MaxEndPost int NOT NULL
    , MaxEndPostPlus decimal(38,3) NOT NULL
    , PostUnits int NOT NULL
);


INSERT INTO dbo.SurveyData (StartPostStation, StartPostPlus, EndPostStation, EndPostPlus
    , MinStartPost, MinStartPostPlus, MaxStartPost, MaxStartPostPlus
    , MinEndPost, MinEndPostPlus, MaxEndPost, MaxEndPostPlus
    , PostUnits)
VALUES (120, 49.2, 175, 80.5  --measurements
    , 100, 50.3, 150, 20.65   --valid start post range
    , 150, 60.3, 500, 20.75   --valid end post range
    , 100); --units per post

Selecting data would look like this:

SELECT StartPost = CONVERT(varchar(10), sd.StartPostStation) + '+' + CONVERT(varchar(50), sd.StartPostPlus)
    , EndPost = CONVERT(varchar(10), sd.EndPostStation) + '+' + CONVERT(varchar(50), sd.EndPostPlus)
FROM dbo.SurveyData sd;

Results, like this:

+------------+------------+
| StartPost  |  EndPost   |
+------------+------------+
| 120+49.200 | 175+80.500 |
+------------+------------+

The post columns have been split into two to enable us to use the correct data types to store numeric data. If we try to store 175+80.50 in a single field, we end up using a varchar(x) column, which allows all kinds of possibilities for bad data, such as tee+27.-1, which are very difficult to comprehensively prevent. So, we store the station in one column, and the offset from that station in the next column. When presenting this data on screen or on reports, to humans, we'd use the concatenated version shown above.

4
  • Solid code. You are right this would work great for the issue at hand, only thing is this is assuming i have two columns for each column i mentioned. The data entered would be 120+50.50 in one field; not 120 in one column (StartPostStation) and 50.50 in another column (StartPostPlus).
    – SQLserving
    Jan 26, 2017 at 21:09
  • 1
    You can't do it reliably in one column. I used two columns in the table because that allows real data integrity, and showed how to display the items in the desired format for human consumption.
    – Hannah Vernon
    Jan 26, 2017 at 21:13
  • The suggestion is to get the data entered by the users and separate it into the 2 parts, then store it. Jan 26, 2017 at 21:13
  • The data entry screen should parse the data entered (120+50.35) into two columns by splitting it on the + then entering 120 and 50.35
    – Hannah Vernon
    Jan 26, 2017 at 21:16
1
    Create table dbo.road   
    (  
    ID tinyint not null,   
    StartMile varchar(9) not null,  
    CONSTRAINT StartMile CHECK (StartMile LIKE '[0-9][0-9][0-9][+][0-9][0-9][.][0-9][0-9]'),  
    CONSTRAINT StartMile CHECK (((left([Startmile],(3))*(100))+(Convert(decimal(5,2),(right([Startmile],(5))))))>=(12764.76)),
    (((left([Startmile],(3))*(100))+(Convert(decimal(5,2),(right([Startmile],(5))))))<=(30618.84))
    );   
    Insert into dbo.road values (1,'150+60.20');

This worked for me too in addition to the what Max proposed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.