Create a copy table with primary key setting : Copy Table « Table « Oracle PL / SQL

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » Table » Copy Table 
Create a copy table with primary key setting
    
SQL>
SQL> CREATE TABLE departments
  2  (department_id           number(10)            not null,
  3   department_name      varchar2(50)      not null,
  4   CONSTRAINT departments_pk PRIMARY KEY (department_id)
  5  );

Table created.

SQL>
SQL>
SQL>
SQL> insert into departments department_id, department_name )
  2                    values1,             'Data Group' );

row created.

SQL>
SQL> insert into departments department_id, department_name )
  2                    values2,             'Purchasing' );

row created.

SQL>
SQL> insert into departments department_id, department_name )
  2                    values3,             'Call Center' );

row created.

SQL>
SQL> insert into departments department_id, department_name )
  2                    values4,             'Communication' );

row created.

SQL>
SQL>
SQL>
SQL> CREATE TABLE employees
  2  employee_id          number(10)      not null,
  3    last_name            varchar2(50)      not null,
  4    email                varchar2(30),
  5    hire_date            date,
  6    job_id               varchar2(30),
  7    department_id        number(10),
  8    salary               number(6),
  9    manager_id           number(6),
 10    CONSTRAINT           employees_pk PRIMARY KEY (employee_id),
 11    CONSTRAINT           fk_departments FOREIGN KEY (department_idREFERENCES departments(department_id)
 12  );

Table created.

SQL>
SQL>
SQL> insert into employeesemployee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
  2                values 1001'Lawson', 'lawson@g.com', '01-JAN-2002','MGR', 30000,,1004);

row created.

SQL>
SQL> insert into employeesemployee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                values 1002'Wells', 'wells@g.com', '01-JAN-2002', 'DBA', 20000,21005 );

row created.

SQL>
SQL> insert into employeesemployee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
  2                 values1003'Bliss', 'bliss@g.com', '01-JAN-2002', 'PROG', 24000,,1004);

row created.

SQL>
SQL> insert into employeesemployee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values1004,  'Kyte', 'tkyte@a.com', SYSDATE-3650'MGR',25000 ,41005);

row created.

SQL>
SQL> insert into employeesemployee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values1005'Viper', 'sdillon@a .com', SYSDATE, 'PROG', 2000011006);

row created.

SQL>
SQL> insert into employeesemployee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
  2                 values1006'Beck', 'clbeck@g.com', SYSDATE, 'PROG', 200002null);

row created.

SQL>
SQL> insert into employeesemployee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values1007'Java', 'java01@g.com', SYSDATE, 'PROG', 2000031006);

row created.

SQL>
SQL> insert into employeesemployee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
  2                 values1008'Oracle', 'wvelasq@g.com', SYSDATE, 'DBA', 2000041006);

row created.

SQL>
SQL> select from employees;

EMPLOYEE_ID LAST_NAME                                          EMAIL                          HIRE_DATE JOB_ID                         DEPARTMENT_ID     SALARY MANAGER_ID
----------- -------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
       1001 Lawson                                             lawson@g.com                   01-JAN-02 MGR                                        1      30000       1004
       1002 Wells                                              wells@g.com                    01-JAN-02 DBA                                        2      20000       1005
       1003 Bliss                                              bliss@g.com                    01-JAN-02 PROG                                       3      24000       1004
       1004 Kyte                                               tkyte@a.com                    13-JUN-98 MGR                                        4      25000       1005
       1005 Viper                                              sdillon@a .com                 10-JUN-08 PROG                                       1      20000       1006
       1006 Beck                                               clbeck@g.com                   10-JUN-08 PROG                                       2      20000
       1007 Java                                               java01@g.com                   10-JUN-08 PROG                                       3      20000       1006

EMPLOYEE_ID LAST_NAME                                          EMAIL                          HIRE_DATE JOB_ID                         DEPARTMENT_ID     SALARY MANAGER_ID
----------- -------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
       1008 Oracle                                             wvelasq@g.com                  10-JUN-08 DBA                                        4      20000       1006

rows selected.

SQL>
SQL> select from departments;

DEPARTMENT_ID DEPARTMENT_NA
------------- -------------
            Data Group
            Purchasing
            Call Center
            Communication

rows selected.

SQL>
SQL>
SQL>
SQL> create table locations_iot (
  2        employee_id,
  3        manager_id,
  4        primary key employee_id, manager_id )
  5  )
  6  organization index
  7  nocompress
  8  as select e.employee_id, e.manager_id
  9  from employees e, departments d
 10  where d.department_id = e.department_id
 11  /
from employees e, departments d
     *
ERROR at line 9:
ORA-01400: cannot insert NULL into ("SYS"."LOCATIONS_IOT"."MANAGER_ID")


SQL>
SQL>
SQL> select from locations_iot;
select from locations_iot
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL>
SQL> drop table locations_iot;
drop table locations_iot
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> drop table employees cascade constraints;

Table dropped.

SQL> drop table departments cascade constraints;

Table dropped.

SQL>
SQL>

   
    
    
  
Related examples in the same category
1.Use copy command to create new table
2.Create a copy table and insert value to it
3.Copying selected columns from another table
4.Copying selected columns from multiple tables
5.CREATE Table from another table with consitions
6.Duplicate some or all of the data in one table under a different table name
7.Copying table
8.Copying table structure not data
9.Create table as select * from another
10.Create a copy table from all_objects
11.Creating from Another Table
12.The table is created with no rows if the query returned no rows.
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.