How to add together column inward existing tabular array amongst default value is about other pop SQL interview question asked for Junior degree programming labor interviews. Though syntax of SQL query to add together column amongst default value varies piddling chip from database to database, it e'er been performed using ALTER keyword of ANSI SQL. Adding column inward existing tabular array inward MySQL database is rather slow too take forrad too nosotros volition encounter event of SQL query for MySQL database which adds a column amongst default value. You tin likewise render constraints similar NULL or NOT NULL piece adding novel column inward table. In this SQL tutorial we are adding tertiary column inward a tabular array called Contacts which contains name too phone of contacts. Now nosotros desire to add together about other column e-mail amongst default value "abc@yahoo.com". We volition purpose ALTER ascendancy inward SQL to produce that. By the means this is adjacent inward our SQL tutorials e.g. How to bring together 3 tables inward SQL too SQL query to honour duplicate records inward table. If you lot haven’t read them yet, thus you lot may honour them useful.
Add, Modify too Drop Column inward MySQL tabular array amongst ALTER keyword
In this SQL query event nosotros volition encounter : 1) How to add together about other column inward existing tabular array amongst default value inward MySQL database.
2) How to add together column inward a MySQL tabular array amongst NOT NULL constraints
mysql> SELECT * FROM Contacts;
+-------+----------+
| mention | telephone |
+-------+----------+
| James | 80983243 |
| Johny | 67543212 |
| Harry | 12341234 |
| Ron | 44446666 |
+-------+----------+
4 rows IN SET (0.00 sec)
mysql> ALTER TABLE contacts ADD COLUMN e-mail varchar(20) DEFAULT "abc@yahoo.com"
-> ;
Query OK, 4 rows affected (0.20 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM Contacts;
+-------+----------+---------------+
| mention | telephone | e-mail |
+-------+----------+---------------+
| James | 80983243 | abc@yahoo.com |
| Johny | 67543212 | abc@yahoo.com |
| Harry | 12341234 | abc@yahoo.com |
| Ron | 44446666 | abc@yahoo.com |
+-------+----------+---------------+
4 rows IN SET (0.00 sec)
+-------+----------+
| mention | telephone |
+-------+----------+
| James | 80983243 |
| Johny | 67543212 |
| Harry | 12341234 |
| Ron | 44446666 |
+-------+----------+
4 rows IN SET (0.00 sec)
mysql> ALTER TABLE contacts ADD COLUMN e-mail varchar(20) DEFAULT "abc@yahoo.com"
-> ;
Query OK, 4 rows affected (0.20 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM Contacts;
+-------+----------+---------------+
| mention | telephone | e-mail |
+-------+----------+---------------+
| James | 80983243 | abc@yahoo.com |
| Johny | 67543212 | abc@yahoo.com |
| Harry | 12341234 | abc@yahoo.com |
| Ron | 44446666 | abc@yahoo.com |
+-------+----------+---------------+
4 rows IN SET (0.00 sec)
SQL query to drib column inward MySQL table
You tin likewise take away column inward existing tabular array past times using modify tabular array drib column SQL query every bit shown inward below example:
mysql> ALTER TABLE Contacts DROP COLUMN email;
Query OK, 4 rows affected (0.27 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM Contacts;
+-------+----------+
| mention | telephone |
+-------+----------+
| James | 80983243 |
| Johny | 67543212 |
| Harry | 12341234 |
| Ron | 44446666 |
+-------+----------+
4 rows IN SET (0.00 sec)
Query OK, 4 rows affected (0.27 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM Contacts;
+-------+----------+
| mention | telephone |
+-------+----------+
| James | 80983243 |
| Johny | 67543212 |
| Harry | 12341234 |
| Ron | 44446666 |
+-------+----------+
4 rows IN SET (0.00 sec)
SQL query to add together NOT NULL constraints to a column inward MySQL table
Now nosotros volition encounter SQL query to add together about other column inward existing tabular array amongst NOT NULL constraints. When you lot add together column amongst NOT NULL constraints too without default value thus in that place value volition endure empty.
mysql> ALTER TABLE contacts ADD COLUMN e-mail varchar(20) NOT NULL;
Query OK, 18 rows affected (0.22 sec)
Records: 18 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM Contacts;
+-------+----------+-------+
| mention | telephone | e-mail |
+-------+----------+-------+
| James | 80983243 | |
| Johny | 67543212 | |
| Harry | 12341234 | |
| Ron | 44446666 | |
+-------+----------+-------+
4 rows IN SET (0.00 sec)
mysql> INSERT INTO Contacts VALUES ("Ruby", 12345678, NULL);
ERROR 1048 (23000): COLUMN 'email' cannot endure NULL
Query OK, 18 rows affected (0.22 sec)
Records: 18 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM Contacts;
+-------+----------+-------+
| mention | telephone | e-mail |
+-------+----------+-------+
| James | 80983243 | |
| Johny | 67543212 | |
| Harry | 12341234 | |
| Ron | 44446666 | |
+-------+----------+-------+
4 rows IN SET (0.00 sec)
mysql> INSERT INTO Contacts VALUES ("Ruby", 12345678, NULL);
ERROR 1048 (23000): COLUMN 'email' cannot endure NULL
Now you lot tin encounter that e-mail column is non accepting cipher values because its created amongst NOT NULL constraints.
That’s all on How to add, modify too drib column inward a tabular array inward SQL. We receive got seen MySQL database event simply examples are generic too should run on other database every bit good e.g. Oracle, SQL Server too Sybase. Effectively using NULL too NOT NULL constraints tin significantly ameliorate code character of both database too Server. By carefully applying constraints similar NULL too NOT NULL you lot tin effectively validate each inserted tape inward table.
Further Learning
How to honour minute highest salary of Employee inward SQL