Creating a Table by Using a Subquery

Creating a Table by Using a Subquery :

• Create a table and insert rows by combining the CREATE TABLE statement and the AS subquery option.
• Match the numeric of specified columns to the numeric of subquery columns.
• Define columns with column names and default values.
• CREATE TABLE table [(column, column...)] AS subquery;

Violating Constraints

Query :

• UPDATE emp SET department_id = 29 WHERE department_id = 30;

Error :

• insert or update on table "emp" violates foreign key constraint "emp_ref_dept_fk"

DETAIL :

• Key (deptno)=(29) is not present in table "dept"
• Department 29 does not exist

You cannot delete a row that contains a primary key that is used as a foreign key in another table.

Query :

• DELETE FROM departments WHERE department_id = 60;

Error :

• update or delete on table "dept" violates foreign key constraint "emp_re f_dept_fk" on table "emp";

Detail :

• Key (deptno)=(10) is still referenced from table "emp".