Making value required in insert Statement

Question

I want to make ItemCode value in my table required when using the insert statement, I thought of using a NOT NULL constraint but I do not know if that is correct. Here is the structure of my table

CREATE TABLE Item
(
    ID INT auto_increment,
    ItemCode VARCHAR(10) unique NOT NULL,
    ItemDescription VARCHAR(50),
    Price DECIMAL(4,2) DEFAULT 0,

    PRIMARY KEY(ID)
);

I just want to make sure if I did it correctly or not ItemCode value should be required when the user tries to insert value to the table — Osama Natouf, Jul 03 '19 at 04:37
https://stackoverflow.com/questions/27926889/how-do-i-require-a-mysql-field — DatabaseCoder, Jul 03 '19 at 04:44
Just try to `INSERT INTO Item (ItemDescription, Price) VALUES ('Foobar', 1);` You will see that `ItemCode` need a value since it can't be `null` and you did not provide a `default` value. (I vote to close because this doesn't provide any value for StackOverflow, there is no problem here) — AxelH, Jul 03 '19 at 04:53
what about if the user enters just whitespaces can, I prevent that. — Osama Natouf, Jul 03 '19 at 05:00
Normally you would use a check constraint to prevent whitespace only values. But the `auto_increment` suggests you are using MySQL and that still doesn't support check constraints — , Jul 03 '19 at 05:23
Quite often you have to validate data from your front end or if you only have mysql to play with then triggers.Your example is typical where you want to stop the user 'cheating' by entering white space or where the business rules cannot be catered for using the generic tests provided by sql (eg invalid dates in date fields, alpha values going to numeric columns) , foreign key constraints and unique indexes. — P.Salmon, Jul 03 '19 at 05:35

score 0 · Answer 1 · answered Jul 03 '19 at 05:53

0

Using not null will only enforce a value for this field when the record is saved to the database. If the user enters a string of spaces, it will still get saved. You need to put data validation rules in the front end of your application.

answered Jul 03 '19 at 05:53

Programnik

1,449
1
9
13

score 0 · Answer 2 · answered Jul 03 '19 at 11:53

By default, columns in SQL accept NULL values. And, if a value is not provided in an INSERT, then the default is NULL.

When you declare a DEFAULT value ("constraint") for a column, then you can change the value that is inserted, when no value is provided. You haven't declared a DEFAULT, so NULL is used.

Then, NOT NULL prevents a NULL value from being inserted. So, if no value is provided, a DEFAULT value will be used. However, with no DEFAULT an error will occur -- violating the NOT NULL constraint.

In short, your definition does what you want.

Note: Triggers can affect this functionality, so the above assumes that triggers do not use the value in that column.

Making value required in insert Statement

2 Answers2