Oracle PL/SQL/Constraints/Add Primary Key

Материал из SQL эксперт
Перейти к: навигация, поиск

Add Primary Key

   <source lang="sql">
 

SQL> CREATE TABLE Employee(Employee_ID NUMBER(4) NOT NULL,

 2                   Employee_Name VARCHAR2(10),
 3                   JOB VARCHAR2(9),
 4                   Manager_ID NUMBER(4),
 5                   Start_Date DATE,
 6                   Salary NUMBER(7, 2),
 7                   COMM NUMBER(7, 2),
 8                   Deparment_ID NUMBER(2)
 9  );

Table created. SQL> SQL> INSERT INTO Employee VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO Employee VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO Employee VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO Employee VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO Employee VALUES (7654, "MARTIN", "SALESMAN", 7698, TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO Employee VALUES (7698, "BLAKE", "MANAGER", 7839, TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO Employee VALUES (7782, "CLARK", "MANAGER", 7839, TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO Employee VALUES (7788, "SCOTT", "ANALYST", 7566, TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO Employee VALUES (7839, "KING", "PRESIDENT", NULL, TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO Employee VALUES (7844, "TURNER", "SALESMAN", 7698, TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO Employee VALUES (7876, "ADAMS", "CLERK", 7788, TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO Employee VALUES (7900, "JAMES", "CLERK", 7698, TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO Employee VALUES (7902, "FORD", "ANALYST", 7566, TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO Employee VALUES (7934, "MILLER", "CLERK", 7782, TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE TABLE Department(Deparment_ID NUMBER(2),

 2                          Department_Name VARCHAR2(14),
 3                          Location VARCHAR2(13)
 4  );

Table created. SQL> SQL> INSERT INTO Department VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO Department VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO Department VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO Department VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> alter table Employee add constraint Employee_pk primary key(Employee_ID); Table altered. SQL> alter table Department add constraint Department_pk primary key(Deparment_ID); Table altered. SQL> alter table Employee add constraint Employee_fk_Department foreign key(Deparment_ID) references Department; Table altered. SQL> alter table Employee add constraint Employee_fk_Employee foreign key(Manager_ID) references Employee; Table altered. SQL> SQL> select * from Employee; EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID START_DAT SALARY COMM DEPARMENT_ID


---------- --------- ---------- --------- ---------- ---------- ------------
      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

EMPLOYEE_ID EMPLOYEE_N JOB MANAGER_ID START_DAT SALARY COMM DEPARMENT_ID


---------- --------- ---------- --------- ---------- ---------- ------------
      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> select * from Department; DEPARMENT_ID DEPARTMENT_NAM LOCATION


-------------- -------------
         10 ACCOUNTING     NEW YORK
         20 RESEARCH       DALLAS
         30 SALES          CHICAGO
         40 OPERATIONS     BOSTON

SQL> SQL> SQL> SQL> -- Using table aliases in joins SQL> SQL> SELECT e.Employee_ID, e.Start_Date,

 2         e.Employee_Name,d.Department_Name, d.Location
 3  FROM Employee e, Department d
 4  WHERE d.Deparment_ID = e.Deparment_ID;

EMPLOYEE_ID START_DAT EMPLOYEE_N DEPARTMENT_NAM LOCATION


--------- ---------- -------------- -------------
      7369 17-DEC-80 SMITH      RESEARCH       DALLAS
      7499 20-FEB-81 ALLEN      SALES          CHICAGO
      7521 22-FEB-81 WARD       SALES          CHICAGO
      7566 02-APR-81 JONES      RESEARCH       DALLAS
      7654 28-SEP-81 MARTIN     SALES          CHICAGO
      7698 01-MAY-81 BLAKE      SALES          CHICAGO
      7782 09-JUN-81 CLARK      ACCOUNTING     NEW YORK
      7788 09-DEC-82 SCOTT      RESEARCH       DALLAS
      7839 17-NOV-81 KING       ACCOUNTING     NEW YORK
      7844 08-SEP-81 TURNER     SALES          CHICAGO
      7876 12-JAN-83 ADAMS      RESEARCH       DALLAS

EMPLOYEE_ID START_DAT EMPLOYEE_N DEPARTMENT_NAM LOCATION


--------- ---------- -------------- -------------
      7900 03-DEC-81 JAMES      SALES          CHICAGO
      7902 03-DEC-81 FORD       RESEARCH       DALLAS
      7934 23-JAN-82 MILLER     ACCOUNTING     NEW YORK

14 rows selected. SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> drop table Department cascade constraints; Table dropped. SQL> drop table Employee cascade constraints; Table dropped. SQL> SQL>


 </source>
   
  


Add primary key and try to insert default value to it

   <source lang="sql">
  

SQL> SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) ); Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> create table another_dept as select * from dept; Table created. SQL> SQL> alter table another_dept add constraint another_dept_pk primary key( deptno ); Table altered. SQL> SQL> insert into another_dept values( 40, "OPERATIONS", "BOSTON" ); insert into another_dept values( 40, "OPERATIONS", "BOSTON" )

ERROR at line 1: ORA-00001: unique constraint (SYS.ANOTHER_DEPT_PK) violated

SQL> insert into another_dept (loc)values( "RESTON" ); insert into another_dept (loc)values( "RESTON" )

ERROR at line 1: ORA-01400: cannot insert NULL into ("SYS"."ANOTHER_DEPT"."DEPTNO")

SQL> SQL> drop table another_dept; Table dropped. SQL> drop table dept; Table dropped. SQL>


 </source>
   
  


Add primary key as the last statement

   <source lang="sql">
 

SQL> SQL> SQL> CREATE TABLE myTable (

 2     Name    VARCHAR(50) NOT NULL,
 3     PhoneNo VARCHAR(15) DEFAULT "Unknown Phone" NOT NULL,
 4     CONSTRAINT MyPrimaryKey PRIMARY KEY (Name)
 5  );

Table created. SQL> SQL> DROP TABLE myTable; Table dropped.


 </source>
   
  


Alter a table to insert primary key and index

   <source lang="sql">
  

SQL> SQL> create table inventory(

 2  partno number(4),
 3  partdesc varchar2(35),
 4  price number(8,2),
 5  warehouse varchar2(15));

Table created. SQL> SQL> Create index invent_part_loc_idx

 2  On inventory (partno, warehouse);

Index created. SQL> SQL> SQL> Alter table inventory add (

 2  Constraint invent_partno_pk primary key (partno)
 3  Using index invent_part_loc_idx);

Table altered. SQL> SQL> SQL> SQL> drop table inventory; Table dropped. SQL>


 </source>
   
  


Alter table to add primary key

   <source lang="sql">
 

SQL> SQL> create table emp (

 2  id number(6) );

Table created. SQL> SQL> alter table emp

 2  add constraint emp_pk
 3  primary key (id);

Table altered. SQL> SQL> SQL> drop table emp; Table dropped.


 </source>
   
  


Alter table to primary key and check it in user_ind_columns and user_cons_columns

   <source lang="sql">
 

SQL> SQL> set echo off SQL> create table emp(

 2           emp_id            integer
 3          ,lastname               varchar2(20)   constraint lastname_create_nn not null
 4          ,firstname              varchar2(15)   constraint firstname_create_nn 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> create unique index pk_idx on emp (emp_id); Index created. SQL> SQL> alter table emp add (constraint pk_alter primary key(emp_id)); Table altered. SQL> SQL> select index_name, table_name, column_name from user_ind_columns where table_name = "emp"; no rows selected SQL> SQL> select constraint_name, table_name, column_name from user_cons_columns where table_name = "emp"; no rows selected SQL> SQL> drop table emp cascade constraints; Table dropped.


 </source>
   
  


DUP_VAL_ON_INDEX exception.

   <source lang="sql">
  

SQL> SQL> SQL> SQL> CREATE TABLE lecturer (

 2    id               NUMBER(5) PRIMARY KEY,
 3    first_name       VARCHAR2(20),
 4    last_name        VARCHAR2(20),
 5    major            VARCHAR2(30),
 6    current_credits  NUMBER(3)
 7    );

Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10001, "Scott", "Lawson","Computer Science", 11);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)

 2                VALUES (10002, "Mar", "Wells","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10003, "Jone", "Bliss","Computer Science", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10004, "Man", "Kyte","Economics", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10005, "Pat", "Poll","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10006, "Tim", "Viper","History", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10007, "Barbara", "Blues","Economics", 7);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10008, "David", "Large","Music", 4);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10009, "Chris", "Elegant","Nutrition", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10010, "Rose", "Bond","Music", 7);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10011, "Rita", "Johnson","Nutrition", 8);

1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)

 2                VALUES (10012, "Sharon", "Clear","Computer Science", 3);

1 row created. SQL> SQL> BEGIN

 2    INSERT INTO lecturer (id, first_name, last_name)
 3      VALUES (20000, "John", "Smith");
 4    INSERT INTO lecturer (id, first_name, last_name)
 5      VALUES (20000, "Susan", "Ryan");
 6  END;
 7  /

BEGIN

ERROR at line 1: ORA-00001: unique constraint (sqle.SYS_C006710) violated ORA-06512: at line 4

SQL> SQL> select * from lecturer;

     ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS

-------------------- -------------------- ------------------------------ ---------------
                1. Scott Lawson Computer Science 11.00
                2. Mar Wells History 4.00
                3. Jone Bliss Computer Science 8.00
                4. Man Kyte Economics 8.00
                5. Pat Poll History 4.00
                6. Tim Viper History 4.00
                7. Barbara Blues Economics 7.00
                8. David Large Music 4.00
                9. Chris Elegant Nutrition 8.00
                10. Rose Bond Music 7.00
                11. Rita Johnson Nutrition 8.00
     ID FIRST_NAME           LAST_NAME            MAJOR                          CURRENT_CREDITS

-------------------- -------------------- ------------------------------ ---------------
                1. Sharon Clear Computer Science 3.00

12 rows selected. SQL> SQL> drop table lecturer; Table dropped. SQL> SQL>


 </source>
   
  


Joined primary key

   <source lang="sql">
 

SQL> CREATE TABLE store (

 2    store_id VARCHAR2(12) CONSTRAINT store_pk PRIMARY KEY,
 3    store_name VARCHAR2(15),
 4    city VARCHAR2(25),
 5    state VARCHAR2(2),
 6    region VARCHAR2(2),
 7    zip VARCHAR2(5));

Table created. SQL> SQL> CREATE TABLE pc (

 2    pc_id VARCHAR2(12) CONSTRAINT prod_pk PRIMARY KEY,
 3    prod_desc VARCHAR2(15),
 4    upc NUMBER,
 5    brand VARCHAR2(12),
 6    category VARCHAR2(12),
 7    department VARCHAR2(12));

Table created. SQL> SQL> CREATE TABLE promotion (

 2    promo_id varchar2(12) CONSTRAINT promo_pk PRIMARY KEY,
 3    promo_name varchar2(15),
 4    promo_type varchar2(12));

Table created. SQL> SQL> CREATE TABLE FinancialYear (

 2    time_id DATE CONSTRAINT time_pk PRIMARY KEY,
 3    month NUMBER,
 4    quarter NUMBER,
 5    year NUMBER,
 6    season VARCHAR2(12));

Table created. SQL> SQL> CREATE TABLE SALES (

 2    time_id DATE NOT NULL CONSTRAINT time_fk                REFERENCES FinancialYear(time_id),
 3    store_id VARCHAR2(12) NOT NULL CONSTRAINT store_fk      REFERENCES store(store_id),
 4    pc_id VARCHAR2(12)  NOT NULL,
 5    promo_id VARCHAR2(12) NOT NULL CONSTRAINT promo_fk      REFERENCES promotion(promo_id),
 6    units NUMBER,
 7    dollars NUMBER,
 8    cost NUMBER,
 9    CONSTRAINT sales_pk PRIMARY KEY (time_id, store_id, pc_id, promo_id)
10  );

Table created. SQL> SQL> SQL> drop table sales; Table dropped. SQL> drop table FinancialYear; Table dropped. SQL> drop table promotion; Table dropped. SQL> drop table pc; Table dropped. SQL> drop table store; Table dropped.


 </source>
   
  


Setting primary key as declaring the column

   <source lang="sql">
 

SQL> SQL> CREATE TABLE myTable (

 2     Name    VARCHAR(50) PRIMARY KEY NOT NULL,
 3     PhoneNo VARCHAR(15) DEFAULT "Unknown Phone" NOT NULL
 4  );

Table created. SQL> SQL> SQL> drop table myTable; Table dropped.


 </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>