...

View Full Version : How to make inserted row values mandatory for a column?



XmisterIS
01-21-2012, 06:34 PM
Let's say I have a table created like this:



create table mytable (
int x not null default 0,
int required not null,
);


So, how can I declare the column required when I create the table so that a value is mandatory for it or MySQL raises an error?

E.g. so that if I try to do, for example insert into mytable set x=5;, I get an error something like, "You must provide a value for required" (or whatever error MySQL would generate).

Keleth
01-21-2012, 06:59 PM
Well, to start, your create statement is wrong. When defining a row, the column name comes first, then type, so


create table mytable (
x int not null default 0,
required int not null,
);

That's also not a valid INSERT statement: INSERT INTO mytable (x) VALUES (5)

Anyway, if I'm wrong, hopefully someone points it out, but to my knowledge, there is no way to make a column required. MySQL is a database software, made for storing and pulling data. It can do some logic, but most logic should be done on the server/front end software. When you get the information from the user, you should check it, if the information is valid, then insert it. So even if MySQL has a method to check, which I'd love to know, you should be checking the validity of the data before it even gets to the query stage.

XmisterIS
01-21-2012, 07:12 PM
thanks, yes the syntax for creating the table is wrong - it was an innocent typo, I apologise for not being perfect!! Also, the last comma shouldn't be there, but hey-ho, it has nothing to do with the question I asked, so hey-ho!

And yes, you can use the syntax insert into <tablename> set <columnname> = <value>;

I do check the validity of data before inserting it, but one more check can't be a bad thing!

felgall
01-21-2012, 11:41 PM
Any field defined with NOT NULL and which doesn't have a DEFAULT value is required - it MUST have a value supplied in order to be able to create that row even if that value is 0 or ''.

Keleth
01-22-2012, 12:12 AM
thanks, yes the syntax for creating the table is wrong - it was an innocent typo, I apologise for not being perfect!! Also, the last comma shouldn't be there, but hey-ho, it has nothing to do with the question I asked, so hey-ho!

And yes, you can use the syntax insert into <tablename> set <columnname> = <value>;

I apologize. I wouldn't say I'm new, but in the past I was taught those syntaxes were improper. I guess I was taught wrong; learn something new every day.


Any field defined with NOT NULL and which doesn't have a DEFAULT value is required - it MUST have a value supplied in order to be able to create that row even if that value is 0 or ''.

Then something is wrong with my MySQL. I use NOT NULL all the time, and if I don't provide a data point, it simply inserts a 0 for number types and a '' for character types. It doesn't provide any error feedback.

felgall
01-22-2012, 12:44 AM
Then something is wrong with my MySQL. I use NOT NULL all the time, and if I don't provide a data point, it simply inserts a 0 for number types and a '' for character types. It doesn't provide any error feedback.

Well if you don't specify them in the SQL then those are the values that it uses. To make the fields required you need to specify them in the SQL - then there has to be a value supplied for the field (it can still be 0 or '' but it must be supplied.

Keleth
01-22-2012, 12:56 AM
Well if you don't specify them in the SQL then those are the values that it uses. To make the fields required you need to specify them in the SQL - then there has to be a value supplied for the field (it can still be 0 or '' but it must be supplied.

Then that's not required, that simply a field that will contain a value, whether you supply one or not. Its certainly not required in the sense the OP was asking, and is only required in the most technical sense. To say something is required means without it, something cannot function. By giving a field the NOT NULL flag, you're saying it has to have a value, but if I don't give it one, use something. Its essentially saying by putting in NOT NULL, you are implying a default value.

felgall
01-22-2012, 01:55 AM
Its essentially saying by putting in NOT NULL, you are implying a default value.


Any field defined with NOT NULL is required to have a value. That value must come from somewhere so if there is no specified DEFAULT then 0 or '' is used.

SQL assumes that any test for whether the value to be saved is VALID has been done before passing the value to SQL.

Testing if a field is required implies that there are valid and invalid values for that field and so validation needs to be performed prior to calling the SQL. If you don't validate then presumably any value can be considered to be valid and a required text field van have a value of ''. It has a value in that instance and so meets the condition that the field is required to have a value since if it didn't have a value it would be NULL.

The test disallowing '' as a valid value needs to be done in the validation of the data before attempting to send it to the database.

As far as SQL is concerned 0 and '' are values just like any other and a field that is not required to have a value will allow NULL as that is the indicator that the field doesn't have a value. That's why NULL exists - in order to indicate that the field doesn't have a value.

So if you want to make a field required and have the SQL complain if it doesn't then you need to replace any values that you consider to be not a valid value for the field with NULL when you attempt to insert it into the database. Only values defined as NULL in the insert statement don't have values and so will trigger an error if the field is required to have one.

Perhaps the OP is getting mixed up between what required to have a value means as far as SQL is concerned and what are considered to be valid values in whatever language is being used to capture the data and pass it to the database.

felgall
01-22-2012, 01:59 AM
Its essentially saying by putting in NOT NULL, you are implying a default value.


Yes - that' the way SQL works. 0 and '' are both valid values and so meet the criteria for where a field is required to have a value - a field that doesn't have a value is NULL. Any field defined with NOT NULL is required to have a value and that value must come from somewhere. Any field required to have a value therefore has a default value assigned to it in the database. If you have a field where you want to tell the database that it has no value then you need to tell the database that field is NULL as that is what indicates that there is no value.

SQL assumes that any test for whether the value to be saved is VALID has been done before passing the value to SQL. Testing if a field is required implies that there are valid and invalid values for that field and so validation needs to be performed prior to calling the SQL. If you don't validate then presumably any value can be considered to be valid and a required text field van have a value of ''. It has a value in that instance and so meets the condition that the field is required to have a value since if it didn't have a value it would be NULL. The test disallowing '' as a valid value needs to be done in the validation of the data before attempting to send it to the database.

NULL exists in order to indicate that the field doesn't have a value. A '' or 0 IS a value.

So if you want to make a field required and have the SQL complain if it doesn't then you need to replace any values that you consider to be not a valid value for the field with NULL when you attempt to insert it into the database. Only values defined as NULL in the insert statement don't have values and so will trigger an error if the field is required to have one.

Perhaps the OP is getting mixed up between what required to have a value means as far as SQL is concerned and what are considered to be valid values in whatever language is being used to capture the data and pass it to the database.

Old Pedant
01-22-2012, 08:27 AM
KELETH: The odd INSERT INTO table SET field = value syntax is a MySQL-only extension. It's not ANSI SQL, at all, and I personally never use it just because of that. But yes, in MySQL, there are a lot of non-ANSI things, so this isn't all that surprising.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum