Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    Regular Coder
    Join Date
    Jun 2010
    Posts
    293
    Thanks
    63
    Thanked 8 Times in 8 Posts

    How to make inserted row values mandatory for a column?

    Let's say I have a table created like this:

    Code:
    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).

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,537
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Well, to start, your create statement is wrong. When defining a row, the column name comes first, then type, so

    Code:
    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.

  • #3
    Regular Coder
    Join Date
    Jun 2010
    Posts
    293
    Thanks
    63
    Thanked 8 Times in 8 Posts
    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!
    Last edited by XmisterIS; 01-21-2012 at 06:14 PM.

  • #4
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    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 ''.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #5
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,537
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Quote Originally Posted by XmisterIS View Post
    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.

    Quote Originally Posted by felgall View Post
    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.

  • #6
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by Keleth View Post
    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.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #7
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,537
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Quote Originally Posted by felgall View Post
    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.

  • #8
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by Keleth View Post
    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.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #9
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,642
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by Keleth View Post
    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.
    Stephen
    Learn Modern JavaScript - http://javascriptexample.net/
    Helping others to solve their computer problem at http://www.felgall.com/

    Don't forget to start your JavaScript code with "use strict"; which makes it easier to find errors in your code.

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,538
    Thanks
    80
    Thanked 4,490 Times in 4,454 Posts
    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.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •