Scribd
Upload
6 views

MySQL part 5

The document explains the use of the ALTER TABLE statement in MySQL to modify the structure of an existing table, including adding, modifying, or removing attributes, as well as adding constraints like primary keys and unique constraints. It provides syntax and examples for various operations such as adding a new column, changing an attribute's name, and setting default values. Additionally, it covers how to drop a table and a database with corresponding syntax and examples.

Uploaded by

mohitbhagtani05
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views

MySQL part 5

The document explains the use of the ALTER TABLE statement in MySQL to modify the structure of an existing table, including adding, modifying, or removing attributes, as well as adding constraints like primary keys and unique constraints. It provides syntax and examples for various operations such as adding a new column, changing an attribute's name, and setting default values. Additionally, it covers how to drop a table and a database with corresponding syntax and examples.

Uploaded by

mohitbhagtani05
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 17

MySQL Part - 5

ALTER Table
After creating a table we may realize that we
need to add/remove an attribute or to modify
the datatype of an existing attribute or to add
constraint in attribute.
In all such cases, we need to change or alter
the structure of the table by using the ALTER
TABLE statement.
EXAMPLE
Consider the following STUDENT table:

create table student


(rollno int(4),
Name varchar(20),
DateOfBirth date,
AadharNo varchar(12)
);
i) Add an attribute to an existing table
SYNTAX
ALTER TABLE table_name
ADD attribute_name DATATYPE;

EXAMPLE – To add a new column ADDRESS


in an existing table STUDENT of datatype
char
ALTER TABLE STUDENT
ADD ADDRESS CHAR(20);
ii) Modify datatype of an attribute
SYNTAX
ALTER TABLE table_name
MODIFY attribute DATATYPE;

EXAMPLE – To modify the datatype of


column ADDRESS to varchar and size 40
ALTER TABLE STUDENT
MODIFY ADDRESS VARCHAR(40);
iii) Change the name of an attribute
SYNTAX
ALTER TABLE table_name
CHANGE oldattribute newattribute DATATYPE;

EXAMPLE – To change the name of column


ROLLNO to SRNO
ALTER TABLE STUDENT
CHANGE ROLLNO SRNO INT(4);
iv) Remove an attribute
SYNTAX
ALTER TABLE table_name
DROP attribute;

EXAMPLE – To remove the column address


ALTER TABLE STUDENT
DROP ADDRESS;
v) Add primary key to a relation
SYNTAX
ALTER TABLE table_name
ADD PRIMARY KEY (attribute);

EXAMPLE – To set the column SRNO as


primary key
ALTER TABLE STUDENT
ADD PRIMARY KEY(SRNO);
vi) Add unique constraint to an attribute
SYNTAX
ALTER TABLE table_name
ADD UNIQUE(attribute);

EXAMPLE – To set the column AadharNo as


unique
ALTER TABLE STUDENT
ADD UNIQUE(AADHARNO);
vii) Add not null constraint to an attribute
SYNTAX
ALTER TABLE table_name
MODIFY attribute DATATYPE NOT NULL;

EXAMPLE – To set the column Name as not null


ALTER TABLE STUDENT
MODIFY NAME VARCHAR(20) NOT NULL;
viii) Add default value to an attribute
SYNTAX
ALTER TABLE table_name
MODIFY attribute DATATYPE DEFAULT
default_value;

EXAMPLE –To set the default value of column


DateOfBirth as '2017-2-17'
ALTER TABLE STUDENT
MODIFY DateOfBirth date
DEFAULT '2017-2-17';
ix) Add foreign key to a relation

SYNTAX
ALTER TABLE table_name ADD FOREIGN
KEY(attribute name) REFERENCES
referenced_table_name (attribute name);
Create table activity
( activityid varchar(5) primary key,
activityname varchar(20),
srno int(4),
score int(3));

• Considering another table ACTIVITY where


we have to add a foreign key on the column
SRNO, referencing the SRNO column of
STUDENT table.
ALTER TABLE ACTIVITY
ADD FOREIGN KEY(SRNO)
REFERENCES STUDENT(SRNO);
NOTE:
The referenced relation must be already created.

The referenced attribute must be a part of primary key


of the referenced relation.

Data types and size of referenced and referencing


attributes must be same.
x) Remove primary key from the table
SYNTAX
ALTER TABLE table_name
DROP PRIMARY KEY;

EXAMPLE – To remove the primary key from


the STUDENT table.
ALTER TABLE STUDENT
DROP PRIMARY KEY;
Drop a Table
SYNTAX
DROP TABLE table_name;

EXAMPLE
DROP TABLE STUDENT;
Drop a DataBase
SYNTAX
DROP DATABASE database_name;

EXAMPLE
DROP DATABASE SCHOOL;

You might also like