Oracle PL/SQL Tutorial/Table/Primary Key

Материал из SQL эксперт

Перейти к: навигация, поиск

Содержание

All columns as primary key

SQL>
SQL> CREATE TABLE keywords
  2  ( word  varchar2(50),
  3    position   int,
  4    doc_id int,
  5    PRIMARY KEY(word,position,doc_id)
  6  );
TABLE created.
SQL>
SQL> DROP TABLE keywords;
TABLE dropped.


Alter table to insert primary key

SQL> CREATE TABLE department (
  2  dept_id           INTEGER,
  3  dept_name     VARCHAR2(32));
 
SQL>
SQL> ALTER TABLE department
  2  ADD CONSTRAINT pk_dept PRIMARY KEY (dept_id);
TABLE altered.
 
SQL>
SQL> DROP TABLE department;
TABLE dropped.


Create a table with primary key, VARCHAR type column and date type column with default value

CREATE TABLE myTable (        
  2    id INTEGER PRIMARY KEY,
  3    STATUS VARCHAR2(10),
  4    last_modified DATE DEFAULT SYSDATE
  5  );
TABLE created.
SQL>
SQL> DESC myTable;
 Name              NULL?    Type
 ID                NOT NULL NUMBER(38)
 STATUS                     VARCHAR2(10)
 LAST_MODIFIED                                                                                                                                                                  DATE
SQL>
SQL> DROP TABLE myTable;
TABLE dropped.


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

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.


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

SQL>
SQL>
SQL> CREATE TABLE Office
  2     (office_id        VARCHAR2(10) NOT NULL,
  3      building_name    VARCHAR2(20),
  4      PRIMARY KEY (office_id));
TABLE created.
SQL>
SQL> CREATE TABLE Programmer
  2     (lect_id    VARCHAR2(10) NOT NULL,
  3      lect_name  VARCHAR2(20),
  4      office_id  VARCHAR2(10),
  5      PRIMARY KEY (lect_id),
  6      FOREIGN KEY (office_id) REFERENCES Office (office_id)
  7      ON DELETE CASCADE);
TABLE created.
SQL>
SQL> DROP TABLE Office cascade constraints;
TABLE dropped.
SQL> DROP TABLE Programmer cascade constraints;
TABLE dropped.


Try to add two primary keys to one table

SQL>
SQL> -- create demo table
SQL> CREATE TABLE Employee(
  2    ID                 VARCHAR2(4 BYTE)         NOT NULL PRIMARY KEY,
  3    First_Name         VARCHAR2(10 BYTE),
  4    Last_Name          VARCHAR2(10 BYTE),
  5    Start_Date         DATE,
  6    End_Date           DATE,
  7    Salary             Number(8,2),
  8    City               VARCHAR2(10 BYTE),
  9    Description        VARCHAR2(15 BYTE)
 10  )
 11  /
TABLE created.
SQL>
SQL> -- prepare data
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2               VALUES ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary,  City,       Description)
  2                VALUES("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                VALUES("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                VALUES("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
  3  /
1 row created.
SQL> INSERT INTO Employee(ID,  First_Name, Last_Name, Start_Date,                     End_Date,                       Salary, City,        Description)
  2                VALUES("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
  3  /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> SELECT * FROM Employee
  2  /
 
ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01   Jason                Martin               25-JUL-96 25-JUL-06    1234.56 Toronto    Programmer
02   Alison               Mathews              21-MAR-76 21-FEB-86    6661.78 Vancouver  Tester
03   James                Smith                12-DEC-78 15-MAR-90    6544.78 Vancouver  Tester
04   Celia                Rice                 24-OCT-82 21-APR-99    2344.78 Vancouver  Manager
05   Robert               Black                15-JAN-84 08-AUG-98    2334.78 Vancouver  Tester
06   Linda                Green                30-JUL-87 04-JAN-96    4322.78 New York   Tester
07   David                Larry                31-DEC-90 12-FEB-98    7897.78 New York   Manager
 
ID   FIRST_NAME           LAST_NAME            START_DAT END_DATE      SALARY CITY       DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
08   James                Cat                  17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
8 rows selected.
SQL>
SQL> ALTER TABLE employee
  2  ADD constraint emp_pk
  3  PRIMARY KEY( id );
PRIMARY KEY( id )
*
ERROR at line 3:
ORA-02260: TABLE can have only one PRIMARY KEY
 
SQL>
SQL> DESC employee;
 Name            NULL?    Type
------------------------------------------------------------------------
 ID              NOT NULL VARCHAR2(4)
 FIRST_NAME               VARCHAR2(10)
 LAST_NAME                VARCHAR2(10)
 START_DATE               DATE
 END_DATE                 DATE
 SALARY                   NUMBER(8,2)
 CITY                     VARCHAR2(10)
 DESCRIPTION              VARCHAR2(15)
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> DROP TABLE Employee
  2  /
TABLE dropped.


Use combined column as the primary key

SQL>
SQL> CREATE TABLE myTable(
  2  id               number(6) NOT NULL,
  3  salary           number(8,2),
  4  hire_date        date DEFAULT sysdate,
  5  termination_date date,
  6  termination_desc varchar2(4000),
  7  constraint       emphistory_pk PRIMARY KEY (id, hire_date)
  8  );
TABLE created.
SQL>
SQL> DESCRIBE myTable;
 Name                  NULL?    Type
--------------------------------------------------------------------------
 ID                    NOT NULL NUMBER(6)
 SALARY                         NUMBER(8,2)
 HIRE_DATE             NOT NULL DATE
 TERMINATION_DATE               DATE
 TERMINATION_DESC               VARCHAR2(4000)
SQL>
SQL>
SQL> DROP TABLE myTable;
TABLE dropped.


Use three columns as primary key

SQL>
SQL> CREATE TABLE registrations
  2  ( attendee    NUMBER(4)
  3  , course      VARCHAR2(6)
  4  , begindate   DATE
  5  , evaluation  NUMBER(1)
  6  , constraint  R_PK        PRIMARY KEY (attendee,course,begindate)
  7  ) ;
TABLE created.
SQL>
SQL> INSERT INTO registrations VALUES (2,"SQL",date "2009-04-12",4   );
1 row created.
SQL> INSERT INTO registrations VALUES (14,"SQL",date "2009-04-12",5   );
1 row created.
SQL> INSERT INTO registrations VALUES (6,"SQL",date "2009-04-12",4   );
1 row created.
SQL> INSERT INTO registrations VALUES (11,"SQL",date "2009-04-12",2   );
1 row created.
SQL> INSERT INTO registrations VALUES (8,"SQL",date "2009-10-04",NULL);
1 row created.
SQL> INSERT INTO registrations VALUES (9,"SQL",date "2009-10-04",3   );
1 row created.
SQL> INSERT INTO registrations VALUES (13,"SQL",date "2009-10-04",4   );
1 row created.
SQL> INSERT INTO registrations VALUES (13,"SQL",date "2009-12-13",NULL);
1 row created.
SQL> INSERT INTO registrations VALUES (6,"SQL",date "2009-12-13",NULL);
1 row created.
SQL> INSERT INTO registrations VALUES (3,"OAU",date "2009-08-10",4   );
1 row created.
SQL>
SQL> SELECT evaluation
  2  FROM   registrations
  3  WHERE  attendee = 8
  4  ORDER  BY evaluation;
EVALUATION
----------
 
SQL>
SQL> DROP TABLE registrations;
TABLE dropped.