Page 268 - Information_Practice_Fliipbook_Class11
P. 268
On executing the above statement, we get the result as shown in Table 9.8:
+----------+---------------+------+-----+------------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+------------+-------+
| ID | int | NO | PRI | NULL | |
| FName | varchar(20) | NO | | NULL | |
| LName | varchar(20) | NO | | NULL | |
| Gender | char(1) | NO | | NULL | |
| Address | varchar(50) | YES | | NULL | |
| City | varchar(20) | YES | | NULL | |
| Pin_Code | char(6) | YES | | NULL | |
| DOB | date | YES | | NULL | |
| Salary | int | NO | | NULL | |
| Dept_No | int | YES | | NULL | |
| Phone | decimal(10,0) | YES | | NULL | |
| Mobile | decimal(10,0) | YES | | 9999999999 | |
| Email | varchar(50) | NO | | NULL | |
+----------+---------------+------+-----+------------+-------+
Table 9.8: Structure of EMPLOYEE table after modifying Dept_No
Drop Attributes
To drop an attribute from a table, we use the ALTER TABLE statement with the DROP keyword. The syntax of the
ALTER TABLE-DROP statement is as follows:
ALTER TABLE table_name DROP attribute;
For example, to drop the attributes Phone, Mobile, and Email from the EMPLOYEE table, we can execute the
following SQL statements:
ALTER TABLE EMPLOYEE DROP Phone;
ALTER TABLE EMPLOYEE DROP Mobile;
ALTER TABLE EMPLOYEE DROP Email;
As before, we describe the modified table EMPLOYEE as follows:
DESC EMPLOYEE;
On executing the above statement, we get the result as shown in Table 9.9:
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID | int | NO | PRI | NULL | |
| FName | varchar(20) | NO | | NULL | |
| LName | varchar(20) | NO | | NULL | |
| Gender | char(1) | NO | | NULL | |
| Address | varchar(50) | YES | | NULL | |
| City | varchar(20) | YES | | NULL | |
| Pin_Code | char(6) | YES | | NULL | |
| DOB | date | YES | | NULL | |
| Salary | int | NO | | NULL | |
| Dept_No | int | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
Table 9.9: Structure of EMPLOYEE table after dropping the Phone, Mobile, and Email attributes
Add and Remove Primary Key Constraint
Using an ALTER TABLE statement, we can also add and/or remove the primary key constraints. The primary key
constraint can be enforced on an attribute using the following ALTER TABLE statement:
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(ID);
To drop the primary key constraint from an attribute, the following ALTER TABLE statement can be used:
ALTER TABLE EMPLOYEE DROP PRIMARY KEY(ID);
254 Touchpad Informatics Practices-XI

