data validation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

data validation

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2011-04-12 : 06:24:32

I am looking for advice as to the best way to implement data verification. I have a field (ID) which is the PK of a table which is a varchar(11) which should be made up of a combination of a number of other varchar fields
So ID should be same as a concatenation of FieldA varchar(2) + FieldB varchar(3) + FieldC varchar(2) + FieldD varchar(3) + FieldE varchar(1) - and FieldE is optional, the rest are compulsory.
I thought of putting a constraint on ID - or a trigger to check the values after insert/update. Or is it better to do this in the webpage supplying the data?
Any advice appreciated.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-12 : 11:56:46

I don't particularly like the idea of code on a webpage doing enforcing the data integrity. Not because I am a database purist, (God knows I am not), but it just seems like asking for trouble. So I would enforce it in the database itself. Your possible choices I can think of (some of which you listed):

1. Check constraints (probably my preferred way, but with 5 columns involved, I wonder how complex the constraint expression would be).

2. Trigger. Meh! Somehow, I am not a fan of triggers for this purpose.

3. Make the composite column a computed column (persisted perhaps). I have not done this for real, but seems like a workable solution, and the more I think about it, the more I seem to like it. But then I keep asking myself "What could possibly go wrong ?!"

4. If the five columns are non-nullable, make the 5 of them a composite primary key.

Which one of these is the best? My answer is "it depends".

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-12 : 12:28:03

Proof of concept re: primary key on computed column:

CREATE TABLE #a(A VARCHAR(2) NOT NULL, B VARCHAR(3) NOT NULL, C VARCHAR(2) NOT NULL, 
D VARCHAR(3) NOT NULL, E CHAR(1) NOT NULL,
combined AS A+B+C+D+E,
CONSTRAINT PK_#a PRIMARY KEY(combined))

INSERT #a VALUES('A','B','C','D','E')
INSERT #a VALUES('A ','B ','C ','D ','E')

SELECT * FROM #a

DROP TABLE #a

It doesn't need to be persisted in order to work. You do, however, need to consider whether trailing (or leading) spaces in the varchar columns should be considered part of the primary key. (see second INSERT)

My suggestion is to go with sunita's option #4, make all five columns part of the primary key.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-12 : 20:40:57

That's interesting that if the component-columns are non-nullable, you don't have to have a persisted computed column. If I change the create table statement to

CREATE TABLE #a(A VARCHAR(2) NOT NULL, B VARCHAR(3) NOT NULL, C VARCHAR(2) NOT NULL, 
D VARCHAR(3) NOT NULL, E CHAR(1) NOT NULL,
combined AS A+B+C+D+E,
CONSTRAINT PK_#a PRIMARY KEY(combined))

I get this error message:


Msg 1711, Level 16, State 1, Line 2
Cannot define PRIMARY KEY constraint on column 'combined' in table '#a'. The computed column has to be persisted and not nullable.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

But if I do this, then no errors, which makes sense, I guess.

[code]CREATE TABLE #a(A VARCHAR(2) NOT NULL, B VARCHAR(3) NOT NULL, C VARCHAR(2) NOT NULL, 
D VARCHAR(3) NOT NULL, E CHAR(1) NOT NULL,
combined AS isnull(A,'')+B+C+D+E,
CONSTRAINT PK_#a PRIMARY KEY(combined))

So, if the columns are non-nullable, use a composite primary key. If some of the columns are nullable, use a computed column as in the example above. (Yes, that is my final answer!)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-13 : 00:24:48

is this web site .net php ruby or what? also why the concatenation of varchar values for PK? possible, yes probably but good design I doubt it. you will need a cirque du soleil type of coding to handle this. what are you trying to accomplish? what does this web site do?

If you don't have the passion to help people, you have no passion

lappin
Posting Yak Master

182 Posts

Posted - 2011-04-15 : 11:11:15

I agree that this isn't great design. Unfortunately this is a legacy table where the user wanted the ID field to contain business logic - so they would know a number of things about the item just by seeing the id. Of course you get typos when the users enter data so it mixes everything up.
Because this is legacy, my preference would be a trigger on insert /update which would check the fields. Could some-one give me an example of how I could do this?

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-15 : 15:45:35

business logic is one thing good design is another. you can accommodate both. but you cannot design the data logic based on the whim of your manager/user. user does not drive back end design. you can accommodate user preference with elegant design imho. so you can what the user wants for visual but in the back end you can do your thing.
can you change the design to accommodate the user preference but the back end is what you need

If you don't have the passion to help people, you have no passion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-16 : 09:03:29

I sympathize and empathize with you, lappin. Like that great Western philosopher's mother once said "A SQL programmer's job is like a box of chocolates. You never know what you're gonna get." You have to deal with the database you are handed, and so I understand why you need to do this AMA from yosiasz, rob and me.

So here is how the trigger can be implemented. I tested this a little bit, seemed to work, please test for yourself.

CREATE TABLE dbo.TestK
(
   colA  VARCHAR(2) NOT NULL,
   colB  VARCHAR(3) NOT NULL,
   colC  VARCHAR(2) NOT NULL,
   colD  VARCHAR(3) NOT NULL,
   colE  VARCHAR(1) NULL,
   colK  VARCHAR(11) NOT NULL PRIMARY KEY
);
GO

CREATE TRIGGER TestK_Trigger
ON dbo.TEstK
FOR  INSERT, UPDATE
AS
   IF EXISTS 
      (
          SELECT
             *
          FROM
             INSERTED i
          WHERE
             i.colA <> SUBSTRING(i.colK, 1, 2)
             OR  i.colB <> SUBSTRING(i.colK, 3, 3)
             OR  i.colC <> SUBSTRING(i.colK, 6, 2)
             OR  i.colD <> SUBSTRING(i.colK,8, 3)
             OR  (i.colE IS NOT NULL AND i.colE <> SUBSTRING(i.colK, 11, 1))
             OR (i.colE IS NULL AND LEN(i.colK) > 10)
      )
       ROLLBACK TRANSACTION
GO

INSERT INTO TestK VALUES ('12','abc','xy','789','z','12abcxy789z');
INSERT INTO TestK VALUES ('12','abc','xy','789',null,'12abcxy789x');

GO

DROP TABLE dbo.TestK;
GO

lappin
Posting Yak Master

182 Posts

Posted - 2011-05-04 : 09:37:20

Excellent thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources