SQL DEFAULT Constraint
The DEFAULT constraint is used to provide a default value for a column.
The default value will be added to all new records IF no other value is specified.
SQL DEFAULT on CREATE TABLE
CREATE TABLE ram3
(
id INT DEFAULT 99,
name VARCHAR(20) DEFAULT 'no name provided'
);
----------
CREATE TABLE ram4
(
id INT NOT NULL DEFAULT 99,
name VARCHAR(20) NOT NULL DEFAULT 'no name provided'
);
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 |
| ram1 |
+-------------------+
3 rows in set (0.05 sec)
MariaDB [vlrinst]> desc ram; desc ram1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.06 sec)
+-------+-------------+------+-----+---------+-------+
| 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]> create table ram3(
-> id int default 444,
-> name varchar(20) default 'no name'
-> );
Query OK, 0 rows affected (0.33 sec)
MariaDB [vlrinst]> desc ram3;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | 444 | |
| name | varchar(20) | YES | | no name | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
MariaDB [vlrinst]> insert into ram3 values(22,'kiran');
Query OK, 1 row affected (0.10 sec)
MariaDB [vlrinst]> select * from ram3;
+------+-------+
| id | name |
+------+-------+
| 22 | kiran |
+------+-------+
1 row in set (0.02 sec)
MariaDB [vlrinst]> insert into ram3(id) values(22);
Query OK, 1 row affected (0.07 sec)
MariaDB [vlrinst]> select * from ram3;
+------+---------+
| id | name |
+------+---------+
| 22 | kiran |
| 22 | no name |
+------+---------+
2 rows in set (0.00 sec)
MariaDB [vlrinst]> insert into ram3() values();
Query OK, 1 row affected (0.06 sec)
MariaDB [vlrinst]> select * from ram3;
+------+---------+
| id | name |
+------+---------+
| 22 | kiran |
| 22 | no name |
| 444 | no name |
+------+---------+
3 rows in set (0.00 sec)
MariaDB [vlrinst]> create table ram4(
-> id int not null default 2,
-> name varchar not null default "no name"
-> );
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 'not
null default "no name"
)' at line 3
MariaDB [vlrinst]> );CREATE TABLE ram4
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 ')'
at line 1
-> (
-> id INT NOT NULL DEFAULT 99,
-> name VARCHAR(20) NOT NULL DEFAULT 'no name provided'
->
-> );
Query OK, 0 rows affected (0.40 sec)
MariaDB [vlrinst]> desc ram4;
+-------+-------------+------+-----+------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+------------------+-------+
| id | int(11) | NO | | 99 | |
| name | varchar(20) | NO | | no name provided | |
+-------+-------------+------+-----+------------------+-------+
2 rows in set (0.01 sec)
MariaDB [vlrinst]> insert into ram4() values();
Query OK, 1 row affected (0.04 sec)
MariaDB [vlrinst]> select * from ram4;
+----+------------------+
| id | name |
+----+------------------+
| 99 | no name provided |
+----+------------------+
1 row in set (0.00 sec)
MariaDB [vlrinst]> insert into ram4(id,name) values(3,null);
ERROR 1048 (23000): Column 'name' cannot be null
MariaDB [vlrinst]> insert into ram4(id,name) values(3);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
MariaDB [vlrinst]> insert into ram3(id,name) values(3,null);
Query OK, 1 row affected (0.06 sec)
MariaDB [vlrinst]> select * from ram3;
+------+---------+
| id | name |
+------+---------+
| 22 | kiran |
| 22 | no name |
| 444 | no name |
| 3 | NULL |
+------+---------+
4 rows in set (0.00 sec)
MariaDB [vlrinst]> select * from ram3;
+------+---------+
| id | name |
+------+---------+
| 22 | kiran |
| 22 | no name |
| 444 | no name |
| 3 | NULL |
+------+---------+
4 rows in set (0.00 sec)
MariaDB [vlrinst]>