Oracle PL/SQL/Table/Alter Table
Содержание
- 1 Alter table cache
- 2 Alter table to add a foreign key ON DELETE SET NULL
- 3 Alter table to add Columns in Tables
- 4 Alter table to add two columns to a table
- 5 Alter table to change the storage
- 6 Alter table to drop constraints
- 7 Alter table to drop two columns in a single command
- 8 Alter table to drop unused columns
- 9 Alter to add DELETE ON NULL with more than one column
- 10 If you are adding more than one column, the column definitions should be enclosed in parentheses and separated by commas.
- 11 Make myCode a CACHE table.
- 12 Marking Columns Unused
- 13 Use alter table command to add foreign key constraint
- 14 Use alter table command to add foreign key with more than two columns
- 15 Use alter table to add foreign key with cascade delete
- 16 Use alter table to add foreign key with cascade delete for more than one column
Alter table cache
SQL>
SQL> create table emp(
2 emp_id 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 ,company_name varchar2(50));
Table created.
SQL>
SQL> alter table emp cache;
Table altered.
SQL>
SQL> drop table emp;
Table dropped.
Alter table to add a foreign key ON DELETE SET NULL
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 supplier_name varchar2(50) not null
5 );
Table created.
SQL>
SQL>
SQL> ALTER TABLE products
2 add CONSTRAINT fk_supplier
3 FOREIGN KEY (supplier_id)
4 REFERENCES supplier(supplier_id)
5 ON DELETE SET NULL;
Table altered.
SQL>
SQL>
SQL>
SQL> desc products;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER(10)
SUPPLIER_ID NOT NULL NUMBER(10)
SUPPLIER_NAME NOT NULL VARCHAR2(50)
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>
SQL>
Alter table to add Columns in Tables
SQL>
SQL> create table people(
2 employee_id number(9),
3 first_name varchar2(15),
4 last_name varchar2(20),
5 email varchar2(25),
6 constraint pk_people primary key (employee_id)
7 );
Table created.
SQL>
SQL> insert into people values (1, "T", "Kyte", "YourName@q.ru");
1 row created.
SQL>
SQL> insert into people values (2, "S", "Viper", "sdillon@q.ru");
1 row created.
SQL>
SQL> insert into people values (3, "C", "Beck", "clbeck@q.ru");
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select * from people;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
----------- --------------- -------------------- -------------------------
1 T Kyte YourName@q.ru
2 S Viper sdillon@q.ru
3 C Beck clbeck@q.ru
SQL>
SQL> alter table people
2 add (
3 phone_number varchar2(10)
4 );
Table altered.
SQL>
SQL> select * from people;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMB
----------- --------------- -------------------- ------------------------- ----------
1 T Kyte YourName@q.ru
2 S Viper sdillon@q.ru
3 C Beck clbeck@q.ru
SQL>
SQL> drop table people;
Table dropped.
SQL>
Alter table to add two columns to a table
SQL>
SQL> create table people(
2 employee_id number(9),
3 first_name varchar2(15),
4 last_name varchar2(20),
5 email varchar2(25),
6 constraint pk_people primary key (employee_id)
7 );
Table created.
SQL>
SQL> insert into people values (1, "T", "Kyte", "YourName@q.ru");
1 row created.
SQL>
SQL> insert into people values (2, "S", "Dillon", "sdillon@q.ru");
1 row created.
SQL>
SQL> insert into people values (3, "C", "Beck", "clbeck@q.ru");
1 row created.
SQL>
SQL> select * from people;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
----------- --------------- -------------------- -------------------------
1 T Kyte YourName@q.ru
2 S Dillon sdillon@q.ru
3 C Beck clbeck@q.ru
SQL>
SQL>
SQL> alter table people
2 add(
3 t_name varchar2(40),
4 st_name varchar2(15)
5 );
Table altered.
SQL>
SQL> select * from people;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL T_NAME ST_NAME
----------- --------------- -------------------- ------------------------- ---------------------------------------- ---------------
1 T Kyte YourName@q.ru
2 S Dillon sdillon@q.ru
3 C Beck clbeck@q.ru
SQL>
SQL> drop table people;
Table dropped.
SQL>
Alter table to change the storage
SQL>
SQL> create table t ( x int ) storage ( FREELISTS 2 );
Table created.
SQL>
SQL> alter table t storage ( FREELISTS 2 );
Table altered.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL> --
Alter table to drop constraints
SQL>
SQL>
SQL> alter table emp
2 drop constraint emp_pk keep index;
SQL>
Alter table to drop two columns in a single command
create table people(
employee_id number(9),
first_name varchar2(15),
last_name varchar2(20),
email varchar2(25),
constraint pk_people primary key (employee_id)
);
insert into people values (1, "T", "Kyte", "YourName@q.ru");
insert into people values (2, "S", "Dillon", "sdillon@q.ru");
insert into people values (3, "C", "Beck", "clbeck@q.ru");
select * from people;
alter table people drop (first_name, last_name);
drop table people;
Alter table to drop unused columns
create table people(
employee_id number(9),
first_name varchar2(15),
last_name varchar2(20),
email varchar2(25),
constraint pk_people primary key (employee_id)
);
insert into people
values (1, "Tom", "Kyte", "YourName@q.ru");
insert into people
values (2, "Sean", "Viper", "sdillon@q.ru");
insert into people
values (3, "Christopher", "Beck", "clbeck@q.ru");
select * from people;
alter table people drop column first_name;
alter table people drop column last_name;
select * from user_unused_col_tabs;
ALTER TABLE people DROP UNUSED COLUMNS;
drop table people;
Alter to add DELETE ON NULL with more than one column
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, supplier_name)
6 );
Table created.
SQL>
SQL>
SQL> CREATE TABLE products
2 ( product_id numeric(10) not null,
3 supplier_id numeric(10) not null,
4 supplier_name varchar2(50) not null
5 );
Table created.
SQL>
SQL>
SQL> ALTER TABLE products
2 add CONSTRAINT fk_supplier
3 FOREIGN KEY (supplier_id, supplier_name)
4 REFERENCES supplier(supplier_id, supplier_name)
5 ON DELETE SET NULL;
Table altered.
SQL>
SQL>
SQL> desc products;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER(10)
SUPPLIER_ID NOT NULL NUMBER(10)
SUPPLIER_NAME NOT NULL VARCHAR2(50)
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>
SQL>
If you are adding more than one column, the column definitions should be enclosed in parentheses and separated by commas.
SQL>
SQL>
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(6, "Black", "Designer", 9,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL>
SQL>
SQL> ALTER TABLE emp ADD (quantity NUMBER (13,3),
2 update_dt DATE DEFAULT sysdate);
Table altered.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
Make myCode a CACHE table.
SQL>
SQL> create table myCode
2 (
3 codeValue VARCHAR2(1) primary key,
4 Description VARCHAR2(25)
5 );
Table created.
SQL>
SQL> alter table myCode cache;
Table altered.
SQL>
SQL> drop table myCode;
Table dropped.
Marking Columns Unused
create table people(
employee_id number(9),
first_name varchar2(15),
last_name varchar2(20),
email varchar2(25),
constraint pk_people primary key (employee_id)
);
insert into people values (1, "T", "Kyte", "YourName@q.ru");
insert into people values (2, "S", "Dillon", "sdillon@q.ru");
insert into people values (3, "C", "Beck", "clbeck@q.ru");
select * from people;
alter table people
set unused (first_name, last_name);
drop table people;
Use alter table command to add foreign key constraint
SQL>
SQL> -- Use alter table command to add foreign key constraint
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, supplier_name)
6 );
Table created.
SQL>
SQL>
SQL> CREATE TABLE products
2 ( product_id numeric(10) not null,
3 supplier_id numeric(10) not null,
4 supplier_name varchar2(50) not null
5 );
Table created.
SQL>
SQL> ALTER TABLE products
2 add CONSTRAINT fk_supplier
3 FOREIGN KEY (supplier_id, supplier_name)
4 REFERENCES supplier(supplier_id, supplier_name);
Table altered.
SQL>
SQL>
SQL> desc products;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER(10)
SUPPLIER_ID NOT NULL NUMBER(10)
SUPPLIER_NAME NOT NULL VARCHAR2(50)
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>
SQL>
Use alter table command to add foreign key with more than two columns
SQL> -- use alter table command to add foreign key with more than two columns acting as the 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, supplier_name)
6 );
Table created.
SQL>
SQL>
SQL> CREATE TABLE products
2 ( product_id numeric(10) not null,
3 supplier_id numeric(10) not null,
4 supplier_name varchar2(50) not null
5 );
Table created.
SQL>
SQL> ALTER TABLE products
2 add CONSTRAINT fk_supplier
3 FOREIGN KEY (supplier_id, supplier_name)
4 REFERENCES supplier(supplier_id, supplier_name);
Table altered.
SQL>
SQL>
SQL> desc products;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER(10)
SUPPLIER_ID NOT NULL NUMBER(10)
SUPPLIER_NAME NOT NULL VARCHAR2(50)
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>
SQL>
SQL> \
Use alter table to add foreign key with cascade delete
SQL>
SQL> -- Use alter table to add foreign key with cascade delete
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 supplier_name varchar2(50) not null
5 );
Table created.
SQL>
SQL>
SQL> ALTER TABLE products
2 add CONSTRAINT fk_supplier
3 FOREIGN KEY (supplier_id)
4 REFERENCES supplier(supplier_id)
5 ON DELETE CASCADE;
Table altered.
SQL>
SQL>
SQL> desc products;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER(10)
SUPPLIER_ID NOT NULL NUMBER(10)
SUPPLIER_NAME NOT NULL VARCHAR2(50)
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>
SQL>
Use alter table to add foreign key with cascade delete for more than one column
SQL>
SQL> -- Use alter table to add foreign key with cascade delete for more than one column
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, supplier_name)
6 );
Table created.
SQL>
SQL>
SQL> CREATE TABLE products
2 ( product_id numeric(10) not null,
3 supplier_id numeric(10) not null,
4 supplier_name varchar2(50) not null
5 );
Table created.
SQL>
SQL>
SQL>
SQL> ALTER TABLE products
2 add CONSTRAINT fk_supplier
3 FOREIGN KEY (supplier_id, supplier_name)
4 REFERENCES supplier(supplier_id, supplier_name)
5 ON DELETE CASCADE;
Table altered.
SQL>
SQL> desc products;
Name Null? Type
----------------------------------------------------------------------------------------------------- -------- --------------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER(10)
SUPPLIER_ID NOT NULL NUMBER(10)
SUPPLIER_NAME NOT NULL VARCHAR2(50)
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>
SQL>