Verification of Field Limits

[sunjomar]

I am new in the world of SQL and started by reading the whole handbook on MySQL. As I was soon frustrated by entering code on a black background, I moved to SQLyog to improve the speed of setting up a complex database.

Previously I did database structuring with Lotus Approach and found this program very useful in designing a database for our firm. It was only when we moved to a internet-driven database when I learnt that I need to move to SQL and here I am today.

Now I am trying to do in SQL what I am doing for years in Approach and what a battle, I must admit. Please help. I have build a table with different fields, most of them limited by size, unique, Not Null, etc. If I did this in Approach and some one would fill in a field limited to say 8 characters with more letters, Approach would not allow the user to continue entering data until the field's lenght is 8 characters or less. The same applied with any other limitations. For some reason SQLyog only give an error message when duplication a primary field as not being unique, but does not complain when the field are longer than 8 characters or for that matter null.

Going back to the data, only the first 8 characters were recorded, while a search for null fields brings up nothing. Somehow the null field are made not null and the longer fields are truncated to 8 characters. Firstly, what can I do to warn the user entering the data that he or she is not moving within the parameters designed into the database? Secondly, what can I do to get any warning message linked to a customised warning message on my internet site?

Thank you very much for any useful support given.

[Ritesh]

Hello

SQLyog does not check for these errors. It will only give error if MySQL returns an error. By default MySQL truncates data whereever necessary.

It is the job of the end-user ( in this case the user of SQLyog to see that they dont give data more then what is required ) 😀

We do plan some simple verification in future releases of SQLyog but as of now you have to make sure that you dont give any unwanted data

HTH

[CalEvans]

Sunjomar,

I would suggest that if this is important to you, you design an interface to your database in a program that will allow you to do this. I do this in PHP however, I have used Visual FoxPro to interface with MySQL on rare occasions and I've been told that Access can do this. If your database is primarily accessed over the Internet then I would suggest building your user management interface in the same language/script that you developed your web pages in. It is, however, up to you, the developer, to enforce data validation. MySQL or any database engine will simply allow you to build the constraints and then fail gracefully if your users submit data that does not meet those constraints.

As Ritesh said, it is not the place of SQLyog to do this. Programs like Lotus Approach and M$ Access do this by mixing a database front-end with the back-end engine. While this makes for simple programming it makes it very difficult (as you are finding) to migrate these programs and your skills to a true database engine.

SQLyog is a development tool and not a end-user tool for the most part. It and programs like it are designed to allow developers to manipulate data and DDL easily without having to code their programs in a language. However, once you move outside of the realm of developers it quickly becomes useless or worse, dangerous.

HTH,

=C=

[Author]

Posts