Three foreign keys in a table : Add Foreign Key « Constraints « Oracle PL / SQL

1. Oracle PL / SQL
2. Constraints
3. Add Foreign Key

Three foreign keys in a table

    

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.

   
    
    
    
  

Related examples in the same category