MySQL Temporary Table With Constraints

Temporary tables with constraints and indexes can be created in MySQL. Constraints can be defined even if the temporary table is created without giving column names and with a SELECT statement. Take a look at the samples below.

Consider the source table is the popular “emp” table.

Example1: With Column Names
CREATE TEMPORARY TABLE empTemp (
empno INTEGER NOT NULL AUTO_INCREMENT,
PRIMARY KEY(empno),
ename VARCHAR(20))
SELECT empno, ename FROM emp WHERE deptno=10;

Example2: Without Defining Column Names
CREATE TEMPORARY TABLE tempEmpDept (
PRIMARY KEY(empno), INDEX(deptno))
SELECT emp.empno, emp.deptno, ename, dname FROM emp
JOIN dept ON emp.deptno=dept.deptno;

Hope you find this post informative.

Please subscribe for more interesting updates.