SQL NOT NULL Constraint in telugu 13

 https://youtu.be/tYJ_U3JEG-o

-----------------------------------------

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]>