Oracle PL/SQL/Constraints/Add Foreign Key

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

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


Alter table to add primary key and alter another table to add foreign key

   <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> create table courses ( 

 2  course_id   number not null,
 3  course_name varchar2(60) not null,
 4  subject_id  number not null,
 5  duration    number(2),
 6  skill_lvl   varchar2(12) not null
 7  )
 8  tablespace users;

Table created. SQL> SQL> alter table courses 

 2  add constraint pk_courses
 3  primary key (course_id);

Table altered. SQL> SQL> alter table courses 

 2  add constraint fk_course_subj
 3  foreign key (subject_id) references subjects (subject_id);

Table altered. SQL> SQL> alter table courses 

 2  add constraint ck_level check(
 3  skill_lvl in ("BEGINNER", "INTERMEDIATE", "ADVANCED")
 4  );

Table altered. SQL> SQL> drop table courses cascade constraints; Table dropped. SQL> drop table subjects cascade constraints; Table dropped. SQL> 


 </source>


Many to many using a primary-key and foreign-key relationship

   <source lang="sql">

SQL> CREATE TABLE Course 

 2     (course_id        VARCHAR2(10) NOT NULL,
 3      course_name      VARCHAR2(20),
 4      PRIMARY KEY (course_id));

Table created. SQL> SQL> CREATE TABLE emp 

 2     (stud_id    VARCHAR2(10) NOT NULL,
 3      stud_name  VARCHAR2(20),
 4      PRIMARY KEY (stud_id));

Table created. SQL> SQL> CREATE TABLE Enrolls_in 

 2     (course_id  VARCHAR2(10) NOT NULL,
 3      stud_id    VARCHAR2(10) NOT NULL,
 4      PRIMARY KEY (course_id, stud_id),
 5      FOREIGN KEY (course_id) REFERENCES Course (course_id)
 6      ON DELETE CASCADE,
 7      FOREIGN KEY (stud_id) REFERENCES emp (stud_id)
 8      ON DELETE CASCADE);

Table created. SQL> SQL> drop table Course cascade constraints; Table dropped. SQL> drop table emp cascade constraints; Table dropped. SQL> drop table Enrolls_in; Table dropped. SQL> 



 </source>


One to many using a primary-key and foreign-key relationship

   <source lang="sql">

SQL> SQL> SQL> CREATE TABLE Programmer 

 2     (lect_id    VARCHAR2(10) NOT NULL,
 3      lect_name  VARCHAR2(20),
 4      PRIMARY KEY (lect_id));

Table created. SQL> SQL> CREATE TABLE Course 

 2     (course_id        VARCHAR2(10) NOT NULL,
 3      course_name      VARCHAR2(20),
 4      lect_id          VARCHAR(10),
 5      PRIMARY KEY (course_id),
 6      FOREIGN KEY (lect_id) REFERENCES Programmer (lect_id)
 7      ON DELETE CASCADE);

Table created. SQL> SQL> drop table Programmer cascade constraints; Table dropped. SQL> drop table Course cascade constraints; Table dropped. 



 </source>


syntax to reference foreign key

   <source lang="sql">

SQL> SQL>

SQL> --CREATE TABLE OF <object schema> SQL> -- (key attribute NOT NULL, SQL> -- attribute attribute type, SQL> -- PRIMARY KEY (key attribute), SQL> -- FOREIGN KEY (key attribute) SQL> -- REFERENCES <referenced table schema>(key attribute) SQL> -- [ON DELETE][CASCADE|SET NULL]); SQL> SQL> --Example: SQL> SQL> CREATE TABLE emp 2 (id VARCHAR2(10) NOT NULL, 3 course VARCHAR2(10), 4 year VARCHAR2(4), 5 PRIMARY KEY (id), 6 FOREIGN KEY (id) REFERENCES Person ON DELETE CASCADE); SQL> FOREIGN KEY (id) REFERENCES Person ON DELETE CASCADE) SP2-0734: unknown command beginning "FOREIGN KE..." - rest of line ignored. SQL> SQL> SQL> SQL> </source>

Three foreign keys in a table

   <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>
Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL/Constraints/Add_Foreign_Key&oldid=2537»