User friendly translations of constraints...

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 2000 Forums

SQL Server Development (2000)

User friendly translations of constraints...

Author

Topic

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-06-20 : 13:25:05

Our system is designed in such a way that if there is a contraint violation the component queries a table that stores metadata for the constraint. The problem is we're (developers) are not in the habit of updating this table everytime we create a constraint. Does anyone out here have any script that already queries the constraints in a given database and attempts to translate them to user friendly descriptions? For example, say I have a table called Employee and the rule is that FirstName cannot be null or empty. So my constraint would be something like "ISNULL(FirstName, '') <> ''". I would want that to say something like "The value for the first name must be specified".

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-20 : 13:55:19

You could put the constraints in to triggers and manage it that way.

Brett

8-)

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-06-20 : 16:10:47

quote:

You could put the constraints in to triggers and manage it that way.

I'd get shot for suggesting that

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-20 : 16:55:52

you could try the jive translator to give your constraints more "friendly" descriptions.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=27143

Brett -- someone forwarded that page to me a long time ago ... not sure how THEY found it .

- Jeff

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-06-24 : 12:51:05

I was looking for more "common" english, although this would be a fun joke to put into the system!

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-24 : 13:03:01

quote:

Our system be designed in such some way dat if dair be some contraint violashun
da component queries some table dat sto's metadata 4 da constraint. Da
bug-up-da-ass be we be (down low, developa's) aint in da habit o' updatin' dis
table everytime we create some constraint. Duz anyone out in da house have no
script dat alraidy queries da constraints in some given database an' attempts
t' translate them t'usa' friend-like descripshuns? 4 'esample, say I have some
table called Employee an' da damn rule be dat Firstname caint be null o' empty.
So's mah constraint would be sump'n likes "I-S-N-U-Dubba-L(down low, Firstname,
') <> '". I would want dat t' say sump'n likes "Da value 4 da fust name gots'ta
be specified".

But what do you mean:

quote:

have any script that already queries the constraints in a given database

A constraint is a property of a physical Object (can you put contraints on views?).

You can do this with in the stored proc as well..


If ISNULL(FirstName, '') = ''
BEGIN
		SELECT @Error_Loc = 2
			SELECT @Error_Message = 'Name can not blank or NULL'
			SELECT @Error_Type = 50002
  			GOTO sp_Error
END

Then have an error exit, like:


sp_Error:
Rollback TRAN

Select @Comp_Code = -1, @Comp_msg = @Error_Message, @Load_Ind = 'N'

If @Error_Type = 50001
	BEGIN
		Select @error_message = (Select 'Location: ' + RTRIM(Convert(char(3),@Error_Loc))
					     + '  @@ERROR: ' + RTRIM(Convert(char(6),error))
					     + ' Severity: ' + RTRIM(Convert(char(3),severity))
					     + '  Message: ' + RTRIM(description)
				  	   From master..sysmessages
				 	  Where error = @error_out)
	END
If @Error_Type = 50002
	BEGIN
		Select @Error_Message = 'Location: ' + RTRIM(Convert(char(3),@Error_Loc))
			              + ' Severity:  UserLevel '
				      + ' Message: ' + RTRIM(@Error_Message)
	END


GOTO sp_Exit

Other than that, I'm not sure what you're up to....

What's the front end?

Brett

8-)

Edited by - x002548 on 06/24/2003 13:03:55

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-06-24 : 14:05:18

Fountain,
There's nothing like that out there, and even if there was you would by necessity change every string it auto-generated anyway.

Jonathan
{0}

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2003-06-24 : 19:06:37

quote:

A constraint is a property of a physical Object (can you put contraints on views?).

Constraints are "inherited" from the base tables that are implemented in the view plus any other restriction (WHERE) you place on the view. To enforce these view constraints (the WHERE condition) you must add the WITH CHECK OPTION in its definition.

DavidM

"SQL-3 is an abomination.."

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-06-25 : 09:24:05

Thanks for the input guys but I do think the simple constraints can be accounted for and only minimal editing would be required (if any at all for the simple constraints). Some of the assumptions here I'll shoot down for various reasons:

-constraints in triggers (X002548): Very bad idea unless you like to deal with performance issues. We have several hundred (300 plus) tables in a busy transactional type database.

-"jive" descriptions (jsmith8858): i'd probably get fired, or at least reprimanded!

-putting code in stored procedures (X002548): While this is appealing for the sake of getting the job done, it's not the correct approach given our environment. This approach assumes that there will be only one interface into a specific table OR you have to code it in every interface. That's not the case in our situation. We have batch type jobs in VB that use disconnected recordsets, some batch stored procedures that do direct updates against tables and some simple insert/update procedures used by other interfaces. The code that enforces the rules should be centralized (hence constraints).

-would by necessity change every string it auto-generated anyway (setbasedisthetruepath): you really should have taken the blue pill

. Seriously though, I don't expect to find a script that translates 100% of the constraints but there is no reason I can't write one that takes care of the simple contraints with minimal (if any) editing.

Ok gents, thanks for the ideas, I'll go off and write my own (was hoping to save a little time). Once it's done I'll post it here.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-06-26 : 23:41:05

Ok, after reviewing our table that holds this metadata for the contraints, I came to realize the prior developer did a rush job in designing and implementing it. It keys off the object id of an object. Due to the nature of development (objects get dropped and readded), this metadata is out of sync. Basically, over the past 6 months less than 15% of our constraints have valid references anymore. So, my first step is to redesign this piece. I'll be getting the to scripting/parsing of the constraints at a later date.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources