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
<source lang="sql">
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.
</source>
Alter table to add a foreign key ON DELETE SET NULL
<source lang="sql">
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>
</source>
Alter table to add Columns in Tables
<source lang="sql">
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>
</source>
Alter table to add two columns to a table
<source lang="sql">
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>
</source>
Alter table to change the storage
<source lang="sql">
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> --
</source>
Alter table to drop constraints
<source lang="sql">
SQL> SQL> SQL> alter table emp
2 drop constraint emp_pk keep index;
SQL>
</source>
Alter table to drop two columns in a single command
<source lang="sql">
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;
</source>
Alter table to drop unused columns
<source lang="sql">
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;
</source>
Alter to add DELETE ON NULL with more than one column
<source lang="sql">
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>
</source>
If you are adding more than one column, the column definitions should be enclosed in parentheses and separated by commas.
<source lang="sql">
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.
</source>
Make myCode a CACHE table.
<source lang="sql">
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.
</source>
Marking Columns Unused
<source lang="sql">
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;
</source>
Use alter table command to add foreign key constraint
<source lang="sql">
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>
</source>
Use alter table command to add foreign key with more than two columns
<source lang="sql">
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> \
</source>
Use alter table to add foreign key with cascade delete
<source lang="sql">
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>
</source>
Use alter table to add foreign key with cascade delete for more than one column
<source lang="sql">
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>
</source>
