How To Add, Alter Too Drib Column Alongside Default Value, Non Zilch Constraint – Mysql Database Example
How to add together column inwards existing tabular array amongst default value is around other pop SQL interview question asked for Junior degree programming project interviews. Though syntax of SQL query to add together column amongst default value varies petty fleck from database to database, it ever been performed using ALTER keyword of ANSI SQL. Adding column inwards existing tabular array inwards MySQL database is rather slow in addition to guide frontward in addition to nosotros volition encounter event of SQL query for MySQL database which adds a column amongst default value. You tin too supply constraints similar NULL or NOT NULL acre adding novel column inwards table. In this SQL tutorial we are adding 3rd column inwards a tabular array called Contacts which contains name in addition to phone of contacts. Now nosotros desire to add together around other column electronic mail amongst default value "abc@yahoo.com". We volition role ALTER ascendency inwards SQL to produce that. By the agency this is adjacent inwards our SQL tutorials e.g. How to bring together iii tables inwards SQL in addition to SQL query to abide by duplicate records inwards table. If y'all haven’t read them yet, in addition to then y'all may abide by them useful.
Add, Modify in addition to Drop Column inwards MySQL tabular array amongst ALTER keyword
In this SQL query event nosotros volition encounter : 1) How to add together around other column inwards existing tabular array amongst default value inwards MySQL database.
2) How to add together column inwards a MySQL tabular array amongst NOT NULL constraints
mysql> SELECT * FROM Contacts;
+-------+----------+
| refer | telephone |
+-------+----------+
| James | 80983243 |
| Johny | 67543212 |
| Harry | 12341234 |
| Ron | 44446666 |
+-------+----------+
4 rows IN SET (0.00 sec)
mysql> ALTER TABLE contacts ADD COLUMN electronic 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;
+-------+----------+---------------+
| refer | telephone | electronic 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)
+-------+----------+
| refer | telephone |
+-------+----------+
| James | 80983243 |
| Johny | 67543212 |
| Harry | 12341234 |
| Ron | 44446666 |
+-------+----------+
4 rows IN SET (0.00 sec)
mysql> ALTER TABLE contacts ADD COLUMN electronic 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;
+-------+----------+---------------+
| refer | telephone | electronic 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 inwards MySQL table
You tin too withdraw column inwards existing tabular array yesteryear using modify tabular array drib column SQL query every bit shown inwards 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;
+-------+----------+
| refer | 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;
+-------+----------+
| refer | 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 inwards MySQL table
Now nosotros volition encounter SQL query to add together around other column inwards existing tabular array amongst NOT NULL constraints. When y'all add together column amongst NOT NULL constraints in addition to without default value in addition to then in that place value volition live on empty.
mysql> ALTER TABLE contacts ADD COLUMN electronic mail varchar(20) NOT NULL;
Query OK, 18 rows affected (0.22 sec)
Records: 18 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM Contacts;
+-------+----------+-------+
| refer | telephone | electronic 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 live on NULL
Query OK, 18 rows affected (0.22 sec)
Records: 18 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM Contacts;
+-------+----------+-------+
| refer | telephone | electronic 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 live on NULL
Now y'all tin encounter that electronic mail column is non accepting naught values because its created amongst NOT NULL constraints.
That’s all on How to add, modify in addition to drib column inwards a tabular array inwards SQL. We receive got seen MySQL database event merely examples are generic in addition to should operate on other database every bit good e.g. Oracle, SQL Server in addition to Sybase. Effectively using NULL in addition to NOT NULL constraints tin significantly meliorate code character of both database in addition to Server. By carefully applying constraints similar NULL in addition to NOT NULL y'all tin effectively validate each inserted tape inwards table.
Further Learning
How to abide by bit highest salary of Employee inwards SQL
Komentar
Posting Komentar