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.
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.
An example of creating an index-organized table:
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>
Create an external table
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;
create as select
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>
create as select, then add primary key
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>
Create a table and set storage FREELISTS 2
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> --
Create a table by specifying the storage settings
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>
Create a table with "deferrable initially immediate"
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.
Create a table with ORGANIZATION INDEX
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> --
Create a table with "overflow INCLUDING y"
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> --
Create intermediate table for calculation
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 /
CREATE TABLE AS SELECT with where clause
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>
--
Create table template
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);
Create table with Check Constraints
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> --
Create table with data type: VARCHAR2, Date, Number(8,2)
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>
Create table with foreign key
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>
Create table with three columns
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>
Create table with Unique Constraints
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> --
Creating an External Table
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;
--
Creating an index-organized table
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;
Creating Table and indicate tablespace
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>
Creating Table with combined primary key
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>
include a complete CREATE INDEX clause as part of the CREATE TABLE statement
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>
Use "using index" option when creating a table
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>
Using a CREATE TABLE statement: create a table with primary key
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>