TABLE CREATION
&
DATA MANIPULATION
COMMANDS
A constraints refers to condition or limitation
we apply on any column so that only correct
information will be entered in table.
MySQL allows to apply constraint by two
methods
At the time of table creation
After table creation
PRIMARY KEY : ensures unique value in any column also forces
data entry mandatory. Only one primary key can be applied in one
table
UNIQUE : also allows unique value in any column but it allows
NULL values and can be applied to n times
NOT NULL : it will make data entry mandatory for applied
column i.e. NULL will not be allowed
DEFAULT : it allows to specify any value which will be
automatically inserted in applied column if we not specify
applied column at the time of data entry using INSERT
CHECK : allows to specify range of values that can be entered in
applied column like salary must be greater than 2000, marks must
be greater than 0 or dept must be in given list of values etc.
Note: in mysql the database engine will ignore the
Check constraints.
FOREIGN KEY: allows to establish relationship between
2 tables. Foreign key column will be dependent on PRIMARY KEY
column of another table and allows to enter only those values in
foreign key whose corresponding value exists in PRIMARY KEY
Now lets check PRIMARY KEY is working or not by inserting
duplicate empno
Now lets check NOT NULL is working or not by inserting
NULL value in name column
Now let us check how DEFAULT constraint to use. (Remember to use DEFAULT
CONSTRAINT,The applied column name will not be used with INSERT
Default value
‘Marketing’ is
automatically
inserted
Create another table to store training details of
employee as-
mysql> create table training(empno int,
trainingname varchar(20),startdate date,
enddate date, constraint myfkey foreign
key(empno) references ABCLtd(empno));
Error, empno 3
not in ABCLtd
Now Let us try to insert records in our training table:
Note: after foreign key is applied, we cannot delete any
record or update primary key value in master table because
its related records will be in foreign key table
2 main options available while applying foreign key:
1. ON DELETE CASCASE : it means if any record from master
table is deleted its related records in foreign key table will
also be deleted
2. ON UPDATE CASCADE: it means if primary key value in
master table is changed then it will be automatically
reflected in foreign key table
Column level constraint is given
with column definition With column
definition
Example: create table visitor(vid int primary key,
vname varchar(20));
Table level constraints are given after all column
definition.
Example: create table visitor(vid int primary key,
vname varchar(20), primary key(vid));
MySQL allows us to give names to constraints to that when
error occurs due to constraint violation then this name will
appears to help us in identifying for which column this error
occurs.
Example:mysql> create table training(empno int,
trainingname varchar(20),startdate date, enddate date,
constraint myfkey foreign key(empno) references
ABCLtd(empno));
MySQL allows us to get the structure of table like
list of columns, data type, size and key information
of table using DESC / DESCRIBE command
Example
Python allows us to create either fresh table of table
based on existing table. Now we will see how we can
create table based on existing table like backup of a
table or copy of a table.
Full Copy
Create table XYZLtd as select * from ABCLtd;
Selected Column copy
Create table ABCCorp as select empno, name, salary from
ABCLtd;
Selected Record Copy
Create table ITTable as select * from ABCLtd where
dept=‘IT’;
ALTERTABLE command allows us to
perform the following operations:
Adding new column in existing table
Dropping existing column from table
Modifying column definition in table
Changing the name of column
Adding or dropping constraint after
table creation.
After new column is added, if
you select record it will display
NULL in that column for
previous record, we have to
update it using UPDATE
command
ALTERTABLE ABCLtd drop designation;
IN PLACE OF “FIRST”WE CAN ALSO USE
“AFTER COLUMN NAME”
FOR E.G.TO SET DEPTNO AFTER ENAME
ALTERTABLE EMP MODIFY DEPTNO INT
AFTER ENAME
DROPTABLE[IF EXISTS] tablename
Example
DropTable emp;
Drop table if exists emp;
