Oracle PL/SQL/Table/Create Table
Содержание
- 1 A create-table statement with the attributes of ISBN, title, author, and publisher. The primary key is the ISBN attribute.
- 2 An example of creating an index-organized table:
- 3 Create an external table
- 4 create as select
- 5 create as select, then add primary key
- 6 Create a table and set storage FREELISTS 2
- 7 Create a table by specifying the storage settings
- 8 Create a table with "deferrable initially immediate"
- 9 Create a table with ORGANIZATION INDEX
- 10 Create a table with "overflow INCLUDING y"
- 11 Create intermediate table for calculation
- 12 CREATE TABLE AS SELECT with where clause
- 13 Create table template
- 14 Create table with Check Constraints
- 15 Create table with data type: VARCHAR2, Date, Number(8,2)
- 16 Create table with foreign key
- 17 Create table with three columns
- 18 Create table with Unique Constraints
- 19 Creating an External Table
- 20 Creating an index-organized table
- 21 Creating Table and indicate tablespace
- 22 Creating Table with combined primary key
- 23 include a complete CREATE INDEX clause as part of the CREATE TABLE statement
- 24 Use "using index" option when creating a table
- 25 Using a CREATE TABLE statement: create a table with primary key
A create-table statement with the attributes of ISBN, title, author, and publisher. The primary key is the ISBN attribute.
<source lang="sql">
SQL> SQL> CREATE TABLE Book
2 (isbn VARCHAR2(10) NOT NULL, 3 title VARCHAR2(100), 4 author VARCHAR2(100), 5 publisher VARCHAR2(50), 6 PRIMARY KEY (isbn));
Table created. SQL> SQL> drop table book; Table dropped.
</source>
An example of creating an index-organized table:
<source lang="sql">
SQL> SQL> create table states (
2 state_id varchar2(2), 3 state_name varchar2(20), 4 constraint states_pk primary key (state_id) 5 ) 6 organization index;
Table created. SQL> SQL> SQL> drop table states; Table dropped. SQL> SQL>
</source>
Create an external table
<source lang="sql">
SQL> create table myTable
2 (c1 varchar2(80) )
3 organization external
4 (
5 type oracle_loader
6 default directory infile
7 access parameters
8 (
9 records delimited by newline
10 fields
11 (c1 char(80) )
12 )
13 location ("xml.lst")
14 )
15 /
SQL> SQL> SQL> select * from myTable
2 where rownum <= 5;
SQL> drop table myTable;
</source>
create as select
<source lang="sql">
SQL> SQL> SQL> create table employee(
2 employee_id number, 3 break_reason varchar2(100), 4 break_time interval day(1) to second(2));
Table created. SQL> SQL> insert into employee ( employee_id, break_reason, break_time )
2 values ( 100, "C", 3 TIMESTAMP "2001-09-03 12:47:00.000000" - 4 TIMESTAMP "2001-09-03 13:13:00.000000" );
1 row created. SQL> SQL> insert into employee ( employee_id, break_reason, break_time )
2 values ( 100, "B", 3 TIMESTAMP "2001-09-03 13:35:00.000000" - 4 TIMESTAMP "2001-09-03 13:39:00.000000" );
1 row created. SQL> SQL> insert into employee ( employee_id, break_reason, break_time )
2 values ( 100, "P", 3 TIMESTAMP "2001-09-03 16:30:00.000000" - 4 TIMESTAMP "2001-09-03 17:00:00.000000" );
1 row created. SQL> SQL> insert into employee ( employee_id, break_reason, break_time )
2 values ( 100, "F", 3 TIMESTAMP "2001-09-03 17:00:00.000000" - 4 TIMESTAMP "2001-09-03 17:30:00.000000" );
1 row created. SQL> SQL> select * from employee; EMPLOYEE_ID BREAK_REASON BREAK_TIME
------------------------------ ------------------------------
100 C -0 00:26:00.00
100 B -0 00:04:00.00
100 P -0 00:30:00.00
100 F -0 00:30:00.00
SQL> SQL> SQL> create table another_dept
2 as select * 3 from employee;
Table created. SQL> SQL> SQL> select * from another_dept; EMPLOYEE_ID BREAK_REASON BREAK_TIME
------------------------------ ------------------------------
100 C -0 00:26:00.00
100 B -0 00:04:00.00
100 P -0 00:30:00.00
100 F -0 00:30:00.00
SQL> SQL> drop table another_dept; Table dropped. SQL> drop table employee; Table dropped. SQL>
</source>
create as select, then add primary key
<source lang="sql">
SQL> SQL> create table employee(
2 employee_id number, 3 break_reason varchar2(100), 4 break_time interval day(1) to second(2));
Table created. SQL> SQL> insert into employee ( employee_id, break_reason, break_time )
2 values ( 101, "C", 3 TIMESTAMP "2001-09-03 12:47:00.000000" - 4 TIMESTAMP "2001-09-03 13:13:00.000000" );
1 row created. SQL> SQL> insert into employee ( employee_id, break_reason, break_time )
2 values ( 102, "B", 3 TIMESTAMP "2001-09-03 13:35:00.000000" - 4 TIMESTAMP "2001-09-03 13:39:00.000000" );
1 row created. SQL> SQL> insert into employee ( employee_id, break_reason, break_time )
2 values ( 103, "P", 3 TIMESTAMP "2001-09-03 16:30:00.000000" - 4 TIMESTAMP "2001-09-03 17:00:00.000000" );
1 row created. SQL> SQL> insert into employee ( employee_id, break_reason, break_time )
2 values ( 104, "F", 3 TIMESTAMP "2001-09-03 17:00:00.000000" - 4 TIMESTAMP "2001-09-03 17:30:00.000000" );
1 row created. SQL> SQL> select * from employee; EMPLOYEE_ID BREAK_REASON BREAK_TIME
------------------------------ ------------------------------
101 C -0 00:26:00.00
102 B -0 00:04:00.00
103 P -0 00:30:00.00
104 F -0 00:30:00.00
SQL> SQL> SQL> create table another_dept as select * from employee; Table created. SQL> SQL> alter table another_dept
2 add constraint another_dept_pk 3 primary key(employee_id);
Table altered. SQL> SQL> SQL> select * from another_dept; EMPLOYEE_ID BREAK_REASON BREAK_TIME
------------------------------ ------------------------------
101 C -0 00:26:00.00
102 B -0 00:04:00.00
103 P -0 00:30:00.00
104 F -0 00:30:00.00
SQL> SQL> drop table another_dept cascade constraints; Table dropped. SQL> drop table employee cascade constraints; Table dropped. SQL>
</source>
Create a table and set storage FREELISTS 2
<source lang="sql">
SQL> SQL> create table t ( x int ) storage ( FREELISTS 2 ); Table created. SQL> SQL> begin
2 for i in 1 .. 100000 3 loop 4 insert into t values ( i ); 5 commit ; 6 end loop; 7 end; 8 /
PL/SQL procedure successfully completed. SQL> SQL> drop table t; Table dropped. SQL> SQL> SQL> --
</source>
Create a table by specifying the storage settings
<source lang="sql">
SQL> SQL> SQL> create table my_hash_table (
2 name varchar2(30), 3 value varchar2(4000) ) 4 tablespace users 5 storage ( 6 initial 1M 7 next 512K 8 pctincrease 0 9 minextents 2 10 maxextents unlimited ) 11 /
Table created. SQL> SQL> drop table my_hash_table; Table dropped. SQL>
</source>
Create a table with "deferrable initially immediate"
<source lang="sql">
SQL> create table inventory(
2 partno number(4) constraint partno_pk primary key deferrable initially immediate, 3 partdesc varchar2(35) constraint partdesc_uq unique deferrable initially immediate);
Table created. SQL> SQL> SQL> SQL> drop table inventory; Table dropped.
</source>
Create a table with ORGANIZATION INDEX
<source lang="sql">
SQL> SQL> SQL> SQL> create table addresses
2 ( empno number(4), 3 addr_type varchar2(10), 4 street varchar2(20), 5 city varchar2(20), 6 state varchar2(2), 7 zip number, 8 primary key (empno,addr_type) 9 ) 10 ORGANIZATION INDEX 11 /
Table created. SQL> SQL> drop table addresses; Table dropped. SQL> SQL> --
</source>
Create a table with "overflow INCLUDING y"
<source lang="sql">
SQL> SQL> create table t3
2 ( x int primary key, 3 y varchar2(25), 4 z date 5 ) 6 organization index 7 overflow INCLUDING y;
Table created. SQL> SQL> drop table t3; Table dropped. SQL> SQL> SQL> --
</source>
Create intermediate table for calculation
<source lang="sql">
SQL> create table emp(
2 emp_no integer primary key, 3 lastname varchar2(20) not null, 4 firstname varchar2(15) not null, 5 midinit varchar2(1), 6 street varchar2(30), 7 city varchar2(20), 8 state varchar2(2), 9 zip varchar2(5), 10 shortZipCode varchar2(4), 11 area_code varchar2(3), 12 phone varchar2(8), 13 salary number(5,2), 14 birthdate date, 15 startDate date, 16 title varchar2(20), 17 dept_no integer, 18 mgr integer, 19 region number, 20 division number, 21 total_sales number 22 );
Table created. SQL> -- emp Table Inserts: SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, birthdate, title)values
2 (1,"Z","Joy","R","1 Ave","New York","NY","12122","2333","212","200-1111","12-nov-1976","President");
1 row created. SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, salary, birthdate, startDate,title, dept_no, mgr, region, division, total_sales)valu es
2 (2,"X","Lucy","J","1 Street","New York","NY","43552","6633","212","234-4444",7.75,"21-mar-1976","1-feb-1994","Sales Manager",2,1,100,10,40000);
1 row created. SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, salary, birthdate, startDate,title, dept_no, mgr, region, division, total_sales)valu es
2 (3,"Y","Jordan","E","1 Drive","New York","NY","76822","8763","212","222-2222",7.75,"14-feb-1963","15-mar-1995","Sales Clerk",2,2,100,10,10000);
1 row created. SQL> SQL> create table avg_sal
2 as select avg(salary) AS avg_Sal from emp;
SQL> SQL> SQL> select lastname, salary,
2 CASE WHEN salary > avg_sal THEN "> Average of " || to_char(avg_sal, "99.99") 3 WHEN salary < avg_sal THEN "< Average of " || to_char(avg_sal, "99.99") 4 ELSE "= Average of " || to_char(avg_sal, "99.99") 5 END 6 from emp, avg_sal 7 / </source>
CREATE TABLE AS SELECT with where clause
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected. SQL> SQL> SQL> create table emp_copy as
2 select * from emp where 1 = 0;
Table created. SQL> SQL> select * from emp_copy
2
SQL> SQL> drop table emp; Table dropped. SQL> drop table emp_copy; Table dropped. SQL> SQL> --
</source>
Create table template
<source lang="sql">
create table ( col1 number,
col2 number, ..... )
partition by range ( col1, col2 )
( partition p1 values less than (...,... ) tablespace p1, partition p2 values less than (...,... ) tablespace p2); </source>
Create table with Check Constraints
<source lang="sql">
SQL> --Check Constraints SQL> SQL> create table employee
2 ( employee_id number, 3 last_name varchar2(30), 4 first_name varchar2(30), 5 department_id number, 6 salary number CHECK(salary < 100000));
Table created. SQL> SQL> SQL> drop table employee; Table dropped. SQL> --
</source>
Create table with data type: VARCHAR2, Date, Number(8,2)
<source lang="sql">
SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(20 BYTE), 4 Last_Name VARCHAR2(20 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(20 BYTE), 9 Description VARCHAR2(80 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 2234.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 2324.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 3334.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 4334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 5334.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 6334.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 7334.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- -------------------- --------------------------------------------------------------------------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 2234.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 2324.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 3334.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 4334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 5334.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 6334.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 7334.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SELECT id, first_name, salary
2 FROM employee 3 WHERE salary > 3000 4 ORDER BY salary;
ID FIRST_NAME SALARY
-------------------- ----------
04 Celia 3334.78 05 Robert 4334.78 06 Linda 5334.78 07 David 6334.78 08 James 7334.78 SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL> SQL>
</source>
Create table with foreign key
<source lang="sql">
SQL> SQL> -- create a foreign key SQL> SQL> CREATE TABLE supplier
2 ( supplier_id numeric(10) not null, 3 supplier_name varchar2(50) not null, 4 contact_name varchar2(50), 5 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) 6 );
Table created. SQL> SQL> SQL> CREATE TABLE products
2 ( product_id numeric(10) not null, 3 supplier_id numeric(10) not null, 4 CONSTRAINT fk_supplier 5 FOREIGN KEY (supplier_id) 6 REFERENCES supplier(supplier_id) 7 );
Table created. SQL> SQL> SQL> SQL> desc products;
Name Null? Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- PRODUCT_ID NOT NULL NUMBER(10) SUPPLIER_ID NOT NULL NUMBER(10)
SQL> desc supplier;
Name Null? Type ----------------------------------------------------------------------------------------------------- -------- -------------------------------------------------------------------- SUPPLIER_ID NOT NULL NUMBER(10) SUPPLIER_NAME NOT NULL VARCHAR2(50) CONTACT_NAME VARCHAR2(50)
SQL> SQL> drop table products cascade constraints; Table dropped. SQL> SQL> drop table supplier cascade constraints; Table dropped. SQL>
</source>
Create table with three columns
<source lang="sql">
SQL> CREATE TABLE customers
2 ( 3 id NUMBER, 4 credit_limit NUMBER, 5 email VARCHAR2(30) 6 );
Table created. SQL> SQL> SQL> select id, credit_limit
2 from customers 3 where id = 28983;
no rows selected SQL> SQL> drop table customers; Table dropped. SQL> SQL>
</source>
Create table with Unique Constraints
<source lang="sql">
SQL> SQL> create table department (
2 Dept_no NUMBER(3), 3 Dept_name VARCHAR2(15), 4 Location VARCHAR2(25), 5 CONSTRAINT Dept_name_ukey UNIQUE (Dept_Name,Location));
Table created. SQL> SQL> SQL> drop table department; Table dropped. SQL> SQL> --
</source>
Creating an External Table
<source lang="sql">
CREATE TABLE sales_ext(
product_id NUMBER(6), sale_date DATE, store_id NUMBER(8), quantity_sold NUMBER(8), unit_cost NUMBER(10,2), unit_price NUMBER(10,2))
ORGANIZATION EXTERNAL ( TYPE ORACLE LOADER DEFAULT DIRECTORY ext_data_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE BADFILE log_file_dir:"sales.bad_xt" LOGFILE log_file_dir:"sales.log_xt" FIELDS TERMINATED BY "|" LDRTRIM MISSING FIELD VALUES ARE NULL) LOCATION ("sales.data")) REJECT LIMIT UNLIMITED;
--
</source>
Creating an index-organized table
<source lang="sql">
SQL> -- Creating an index-organized table: SQL> SQL> create table states (
2 state_id varchar2(2), 3 state_name varchar2(20), 4 constraint states_pk 5 primary key (state_id) 6 ) 7 organization index 8 /
Table created. SQL> SQL> drop table states;
</source>
Creating Table and indicate tablespace
<source lang="sql">
SQL> SQL> SQL> create table subjects (
2 subject_id number not null, 3 subject_name varchar2(30) not null, 4 description varchar2(4000) 5 ) 6 tablespace users;
Table created. SQL> SQL> alter table subjects
2 add constraint pk_subjects primary key (subject_id);
Table altered. SQL> SQL> drop table subjects; Table dropped. SQL>
</source>
Creating Table with combined primary key
<source lang="sql">
SQL> create table employee_history
2 (employee_id number(6) not null, 3 salary number(8,2), 4 hire_date date default sysdate, 5 termination_date date, 6 termination_desc varchar2(4000), 7 constraint emphistory_pk 8 primary key (employee_id, hire_date) 9 );
Table created. SQL> SQL> SQL> drop table employee_history; Table dropped. SQL>
</source>
include a complete CREATE INDEX clause as part of the CREATE TABLE statement
<source lang="sql">
SQL> SQL> Create table inventory (
2 partno number(4) constraint invent_partno_pk primary key 3 using index ( 4 create index invent_part_loc_idx 5 on inventory (partno, warehouse) 6 pctfree 10), 7 partdesc varchar2(35), 8 price number(8,2), 9 warehouse varchar2(15));
Table created. SQL> SQL> SQL> drop table inventory; Table dropped. SQL>
</source>
Use "using index" option when creating a table
<source lang="sql">
SQL> SQL> SQL> Create table inventory (
2 partno number(4) constraint invent_partno_pk primary key 3 using index ( 4 create index invent_part_loc_idx 5 on inventory (partno, warehouse) 6 pctfree 10), 7 partdesc varchar2(35), 8 price number(8,2), 9 warehouse varchar2(15));
Table created. SQL> SQL> SQL> SQL> drop table inventory; Table dropped. SQL> SQL>
</source>
Using a CREATE TABLE statement: create a table with primary key
<source lang="sql">
SQL> SQL> --Using a CREATE TABLE statement SQL> SQL> CREATE TABLE supplier
2 ( supplier_id numeric(10) not null, 3 supplier_name varchar2(50) not null, 4 contact_name varchar2(50), 5 CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) 6 );
Table created. SQL> SQL> desc supplier;
Name Null? Type ------------------------------ -------- -------------- SUPPLIER_ID NOT NULL NUMBER(10) SUPPLIER_NAME NOT NULL VARCHAR2(50) CONTACT_NAME VARCHAR2(50)
SQL> SQL> drop table supplier; Table dropped. SQL> SQL>
</source>
