Scribd
Upload
8 views21 pages

013. Table Creation & Manipulation Commands

The document outlines various constraints in MySQL for table creation and data manipulation, including PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT, CHECK, and FOREIGN KEY. It explains how to apply these constraints during and after table creation, as well as their implications on data integrity and relationships between tables. Additionally, it covers commands for altering tables, copying data, and retrieving table structures.

Uploaded by

adityarajeevnair
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
8 views21 pages

013. Table Creation & Manipulation Commands

The document outlines various constraints in MySQL for table creation and data manipulation, including PRIMARY KEY, UNIQUE, NOT NULL, DEFAULT, CHECK, and FOREIGN KEY. It explains how to apply these constraints during and after table creation, as well as their implications on data integrity and relationships between tables. Additionally, it covers commands for altering tables, copying data, and retrieving table structures.

Uploaded by

adityarajeevnair
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/ 21

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;

You might also like