Validation Rule In SQL Server 2005
-
Hi, is there any way to write a validation rule for the field in the datbase design of sql server 2005. Like ms access. i tried to search the net but no luck. so i decided to write triggers on insert and update to validate my data. please, advice me if this is good approach. or if there is anything else we can do in the datbase design. (what i mean by validation rule is to for example for my numeric field i want the data to be in a specific range.)
Hussain Mohammed Saleh Attiya ISP Technical Manager Atyaf Telcom - Bahrain
-
Hi, is there any way to write a validation rule for the field in the datbase design of sql server 2005. Like ms access. i tried to search the net but no luck. so i decided to write triggers on insert and update to validate my data. please, advice me if this is good approach. or if there is anything else we can do in the datbase design. (what i mean by validation rule is to for example for my numeric field i want the data to be in a specific range.)
Hussain Mohammed Saleh Attiya ISP Technical Manager Atyaf Telcom - Bahrain
You can make use of check constraint on your table for this. Something like:
Alter YourTableName
check (intCol > 0 and intCol < 100)Although, you should try and filter out faulty data at the UI only. Why to hit database to know that it is a faulty data? It will make your application slow.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
-
You can make use of check constraint on your table for this. Something like:
Alter YourTableName
check (intCol > 0 and intCol < 100)Although, you should try and filter out faulty data at the UI only. Why to hit database to know that it is a faulty data? It will make your application slow.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
Thanks, oh yeah.. i forgot about the check :sigh: and you are right to validate the data in the UI. which i am aleady doing it. the reason why i want to do the validation in the database design level, is to make sure that we are adding the right data and also, to prevent the other developers from playing with the database. our database is shared one and we are using it many applications. thanks for your reply
Hussain Mohammed Saleh Attiya ISP Technical Manager Atyaf Telcom - Bahrain
-
Hi, is there any way to write a validation rule for the field in the datbase design of sql server 2005. Like ms access. i tried to search the net but no luck. so i decided to write triggers on insert and update to validate my data. please, advice me if this is good approach. or if there is anything else we can do in the datbase design. (what i mean by validation rule is to for example for my numeric field i want the data to be in a specific range.)
Hussain Mohammed Saleh Attiya ISP Technical Manager Atyaf Telcom - Bahrain
You need to think about what you are doing, what are you going to do when you get a validation fail, raise an error. Then what are you going to do, trap the error in your DAL/BusinessObject and pass it back to the UI. This is called PROGRAMMING BY ERROR and is the absolute worst validation model you could build. Put you validation is either the UI or the BusinessObject. Validation does not belong in the database.
Never underestimate the power of human stupidity RAH
-
You need to think about what you are doing, what are you going to do when you get a validation fail, raise an error. Then what are you going to do, trap the error in your DAL/BusinessObject and pass it back to the UI. This is called PROGRAMMING BY ERROR and is the absolute worst validation model you could build. Put you validation is either the UI or the BusinessObject. Validation does not belong in the database.
Never underestimate the power of human stupidity RAH
Thanks for your reply. i agree with you. but for your information, i am not waiting to get the error from that database in order to catch it in my DAL/BusinessObject. i am already doing my validation also in the UI. what i really want to make sure is, the data are inserted right. i dont want to keep any chance for developers to play with the application UI to update the database. now you make me confused. because i am doing the database design. here is another question: if i consider the following in my database design: - primary keys - Forien Keys - Default Values - Check Constraint - indexes - Database Diagrams is it bad database design?
Hussain Mohammed Saleh Attiya ISP Technical Manager Atyaf Telcom - Bahrain
-
Thanks for your reply. i agree with you. but for your information, i am not waiting to get the error from that database in order to catch it in my DAL/BusinessObject. i am already doing my validation also in the UI. what i really want to make sure is, the data are inserted right. i dont want to keep any chance for developers to play with the application UI to update the database. now you make me confused. because i am doing the database design. here is another question: if i consider the following in my database design: - primary keys - Forien Keys - Default Values - Check Constraint - indexes - Database Diagrams is it bad database design?
Hussain Mohammed Saleh Attiya ISP Technical Manager Atyaf Telcom - Bahrain
Yah, I opened my (big mouth) reply before you had replied, got delayed and posted after you. What you are doing is correct and is the reason constraints are in the database, if you were doing this as the primary validation my argument would apply. The additional elements you raise are all valid and should be used for a well designed database, what you cannot do is expect and error. With a FK if you decided to try and write the record and trap the error for a FK violation as the primary validation it would be wrong. Having the FK to enforce data integrity is absolutely valid.
Never underestimate the power of human stupidity RAH
-
Hi, is there any way to write a validation rule for the field in the datbase design of sql server 2005. Like ms access. i tried to search the net but no luck. so i decided to write triggers on insert and update to validate my data. please, advice me if this is good approach. or if there is anything else we can do in the datbase design. (what i mean by validation rule is to for example for my numeric field i want the data to be in a specific range.)
Hussain Mohammed Saleh Attiya ISP Technical Manager Atyaf Telcom - Bahrain
Many ways to do validation in database (1) Constraints (2) SP (3) Triggers
-
Hi, is there any way to write a validation rule for the field in the datbase design of sql server 2005. Like ms access. i tried to search the net but no luck. so i decided to write triggers on insert and update to validate my data. please, advice me if this is good approach. or if there is anything else we can do in the datbase design. (what i mean by validation rule is to for example for my numeric field i want the data to be in a specific range.)
Hussain Mohammed Saleh Attiya ISP Technical Manager Atyaf Telcom - Bahrain
Check Constaint [^] to help. Also you can use Rules that enforce data integrity. Check this out Creating Rules and Defaults[^] Just scroll down a bit in the page to get a cleaner picture about the Rules :)
Niladri Biswas