Creating a table and limiting datatype entry

[sanmikele2]

Hi, I am new to mySQL and I would like to know how i could create a table by code and also when I am selecting datatypes for example int- I would like to limit this number so that you can only enter numbers ranging from 1-10 or for example 0-1000000, or 100000-999999. Also how could i limit a string so that it can only be 4-36 characters long?

Thank you

 

[Andrzejek]

I would like to know how i could create a table by code

I would start here


---- Andy

There is a great need for a sarcasm font.

 

[sanmikele2]

Lol good one but for some reason everything i do is incorrect and my question is mostly how can i limit datatype entry to what is said before- Strings can only be 4-36 characters long and numbers must be limited to 1-10, 0-1000000, 100000-999999.

 

[sanmikele2]

And i tried to search online obviously but i cannot find the answer

 

[bborissov]

That is a job for your front end.
And you always can write trigger that reject records if there is one record that is not correct.

Also, you can check this:

https://docs.microsoft.com/en-us/sq...te-sequence-transact-sql?view=sql-server-2017

Borislav Borissov
VFP9 SP2, SQL Server

 

[Andrzejek]

numbers must be limited to 1-10

Another idea: have a small table with 10 records with valid numbers (1 to 10) in a field set up as Primary Key field. Then use it as Foreign Key field in your main table. DB will not allow you to use anything that is NOT in your small table because of the PK-FK constrain.


---- Andy

There is a great need for a sarcasm font.

 

[Olaf Doschke]

You already have two different answers, as you ask on the database level, specifying a too long string you get an SQL error.

There are essentially three ways to tackle this, a) preventing it before it happens, by knowing these limits beforehand and taking care about not exceeding these limitations with frontend code as suggested by Borislav, b) preventing it from happening by letting the user only pick from pre-stored valid values as Andy suggests and
c) reacting to errors with error handling:

This will show that way:

http://www.mysqltutorial.org/mysql-error-handling-in-stored-procedures/

It's not the easiest to do that, as there are many possible errors, so the usual approach is that given by Borislav and as that happens before you even come to the SQL Server with your string, it's not an SQL question at all but a question how you determine whether input is longer than what is allowed in HTML or Winform, Javascript, Java, whatever.

And by the way, this forum is about Microsoft SQL Server and not Oracle MySQL Server.

There is not one single answer, that's a very basic aspect of any programming, you have much freedom and with that freedom also much responsibility, it's up to you what you pick. It's easy to see that programming a general error handler is not the simplest idea, besides the vast things that could error and that you can't all think of in advance, you also would need to decide if you'd like to simply truncate data and still store it or report that error back. Which is, what is usually done by a general error handling. In most cases the user will not be bothered, the error simply stops execution and the error will be logged for the developer to mend the behaviour at the one or other place.

Most often problems like that don't occur on the database level, because in the first stage data entry goes into frontend code handling it, checking rules like limits, optional vs. mandatory data. It's also normal and not insensible to check rules on several levels. The database should have the interest to reject invalid data not only by the data types but also by constraints you define (which Andy's answer also points out, using a foreign key constraint). But because it's expensive both timewise and resource-wise to only check rules at the database level, you also check such rules in the frontend. This seems to be against encapsulation and single responsibility, but there you go wrong with a too strict interpretation of the SOLID principles. As the database is the object storing this data it would need to be that instance taking that responsibility only, but that's too costly resource wise.

If you don't even know what I'm talking about when I mention SOLID principle. There's a long way in front of you. The first step should be dropping the idea there is one single way to do things. There are best practices, but you do things taking into account all needs and you can't live in the development world by only going with all strict rules, they not rarely contradict each other to show the least problem.

If you begin with databases, it's more important to concentrate on the things, which work, so learn to write unproblematic code. Simply truncate too long input.
Also, on a very technical level, when a database errors, that doesn't harm it, that's not creating damage as a car accident does. Too long strings are rejected from the database without anything to do from your side, too long strings don't overflow and write into other columns.

It's a typically humane cultural habit to avoid errors, you don't necessarily do in programming, exception handling is part of programming and includes expected cases you still don't handle preventive but as aftermath. You'll get a feeling when to do what in the long run.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name