SQL NOT NULL Constraint
By default, a column can hold NULL values.
The NOT NULL constraint enforces a column to NOT accept NULL values.
This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
SQL NOT NULL on CREATE TABLE
The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values when the "Persons" table is created:
Example
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
--------------------
Setting environment for using XAMPP for Windows.
venkat@VENKAT-PC c:\xampp
# mysql -h localhost -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 10.1.37-MariaDB mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> USE vlrinst;
Database changed
MariaDB [vlrinst]> SHOW TABLES;
+-------------------+
| Tables_in_vlrinst |
+-------------------+
| faculty |
| ram |
+-------------------+
2 rows in set (0.06 sec)
MariaDB [vlrinst]> desc ram
-> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.12 sec)
MariaDB [vlrinst]> select * from ram;
+------------+--------------------------------+
| id | name |
+------------+--------------------------------+
| 2 | prav |
| 3 | rame |
| 999 | dfjsfjdshfkjsdhfjksdhfkjdshfjk |
| 999 | dfjsfjdshfkjsdhfjksdhfkjdshfjk |
| 2147483647 | kiran |
| 22 | 45495498 |
| 0 | u33 |
| 123 | kiran |
| 123 | 876767 |
+------------+--------------------------------+
9 rows in set (0.04 sec)
MariaDB [vlrinst]> desc ram;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB [vlrinst]> insert into ram values(222);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
MariaDB [vlrinst]> insert into ram (id)values(222);
Query OK, 1 row affected (0.11 sec)
MariaDB [vlrinst]> select * from ram;
+------------+--------------------------------+
| id | name |
+------------+--------------------------------+
| 2 | prav |
| 3 | rame |
| 999 | dfjsfjdshfkjsdhfjksdhfkjdshfjk |
| 999 | dfjsfjdshfkjsdhfjksdhfkjdshfjk |
| 2147483647 | kiran |
| 22 | 45495498 |
| 0 | u33 |
| 123 | kiran |
| 123 | 876767 |
| 222 | NULL |
+------------+--------------------------------+
10 rows in set (0.00 sec)
MariaDB [vlrinst]> insert into ram (name)values("kavitha");
Query OK, 1 row affected (0.06 sec)
MariaDB [vlrinst]> select * from ram;
+------------+--------------------------------+
| id | name |
+------------+--------------------------------+
| 2 | prav |
| 3 | rame |
| 999 | dfjsfjdshfkjsdhfjksdhfkjdshfjk |
| 999 | dfjsfjdshfkjsdhfjksdhfkjdshfjk |
| 2147483647 | kiran |
| 22 | 45495498 |
| 0 | u33 |
| 123 | kiran |
| 123 | 876767 |
| 222 | NULL |
| NULL | kavitha |
+------------+--------------------------------+
11 rows in set (0.00 sec)
MariaDB [vlrinst]> create table ram1(
-> id int not null,
-> name varchar(25) not null,
-> age int
-> );
Query OK, 0 rows affected (0.26 sec)
MariaDB [vlrinst]> desc ram1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | NULL | |
| name | varchar(25) | NO | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
MariaDB [vlrinst]> insert into ram1(id) vlaues(333);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MariaDB server version for the right syntax to use near 'vla
ues(333)' at line 1
MariaDB [vlrinst]> insert into ram1(id) values(333);
Query OK, 1 row affected, 1 warning (0.06 sec)
MariaDB [vlrinst]> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'name' doesn't have a default value |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [vlrinst]> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'name' doesn't have a default value |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)
MariaDB [vlrinst]> select * from ram1;
+-----+------+------+
| id | name | age |
+-----+------+------+
| 333 | | NULL |
+-----+------+------+
1 row in set (0.00 sec)
MariaDB [vlrinst]> insert into ram1(age) values(33);
Query OK, 1 row affected, 2 warnings (0.07 sec)
MariaDB [vlrinst]> show warnings;
+---------+------+-------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'id' doesn't have a default value |
| Warning | 1364 | Field 'name' doesn't have a default value |
+---------+------+-------------------------------------------+
2 rows in set (0.00 sec)
MariaDB [vlrinst]>
No comments:
Post a Comment
Note: only a member of this blog may post a comment.