Oracle PL/SQL Tutorial/Table/Unique

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

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

Содержание

Adding a UNIQUE Constraint

SQL> -- create demo table
SQL> CREATE TABLE myTable(
  2    id           NUMBER(2),
  3    value        NUMBER(6,2)
  4  )
  5  /
TABLE created.
 
SQL>
SQL> ALTER TABLE myTable
  2  ADD CONSTRAINT uq UNIQUE (id);
TABLE altered.
SQL>
SQL>
SQL> DROP TABLE myTable
  2  /
TABLE dropped.
SQL>


Add unique constaint to new added column

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>
SQL> ALTER TABLE employee
  2      ADD (
  3        ssn varchar2( 9 )
  4      );
TABLE altered.
SQL>
SQL> ALTER TABLE employee
  2  ADD constraint employee_ssn_uk
  3  UNIQUE( ssn );
TABLE altered.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> DROP TABLE Employee
  2  /
TABLE dropped.


Add unique constraint across more than one columns

SQL>
SQL>
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
08   James      Cat        17-SEP-96 15-APR-02    1232.78 Vancouver  Tester
8 rows selected.
SQL>
SQL>
SQL> ALTER TABLE employee
  2  ADD CONSTRAINT emp_unique UNIQUE (
  3      first_name,
  4      last_name,
  5      start_date
  6      );
TABLE altered.
SQL>
SQL>
SQL> ALTER TABLE employee
  2  DROP CONSTRAINT emp_unique;
TABLE altered.
SQL>
SQL>
SQL> -- clean the table
SQL> DROP TABLE Employee
  2  /
TABLE dropped.
SQL>
SQL>


Add unique constraints

SQL>
SQL>
SQL> SET echo off
SQL> CREATE TABLE emp(
  2           emp_id            integer     PRIMARY KEY USING INDEX (CREATE INDEX pk_idx ON emp(emp_id) )
  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)
 14          ,constraint unique_emp_phone UNIQUE(phone) USING INDEX (CREATE INDEX emp_phone_u_idx ON emp(phone))
 15          );
TABLE created.
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.


Add unique constraints for combined columns

SQL>
SQL>
SQL> CREATE TABLE person (
  2       person_code VARCHAR2(3) PRIMARY KEY,
  3       first_name  VARCHAR2(15),
  4       last_name   VARCHAR2(20),
  5       hire_date   DATE
  6       );
TABLE created.
SQL>
SQL> ALTER TABLE person
  2  ADD CONSTRAINT person_unique UNIQUE (
  3       first_name,
  4       last_name,
  5       hire_date
  6       );
TABLE altered.
SQL>
SQL>
SQL> DROP TABLE person;
TABLE dropped.
SQL>
SQL>


ORA-00001: unique constraint (JAVA2S.JOB_UNIQUE_IN_TEAMID) violated

SQL> CREATE TABLE emp
  2  (emp_ID number PRIMARY KEY,
  3   teamid number,
  4   job varchar2(100),
  5   STATUS varchar2(20) CHECK (STATUS IN ("ACTIVE", "INACTIVE"))
  6  );
TABLE created.
SQL>
SQL>
SQL> CREATE UNIQUE INDEX job_unique_in_teamid ON emp
  2  ( case when STATUS = "ACTIVE" then teamid else NULL end,
  3    case when STATUS = "ACTIVE" then job    else NULL end
  4  )
  5  /
INDEX created.
SQL>
SQL> INSERT INTO emp(emp_id,teamid,job,STATUS)VALUES( 1, 10, "a", "ACTIVE" );
1 row created.
SQL>
SQL> INSERT INTO emp(emp_id,teamid,job,STATUS)VALUES( 2, 10, "a", "ACTIVE" );
INSERT INTO emp(emp_id,teamid,job,STATUS)VALUES( 2, 10, "a", "ACTIVE" )
*
ERROR at line 1:
ORA-00001: UNIQUE constraint (sqle.JOB_UNIQUE_IN_TEAMID) violated
 
SQL>
SQL>
SQL> UPDATE emp
  2     SET STATUS = "INACTIVE"
  3    WHERE emp_id = 1
  4      AND teamid = 10
  5      AND STATUS = "ACTIVE";
1 row updated.
SQL>
SQL>
SQL> INSERT INTO emp(emp_id,teamid,job,STATUS)VALUES( 2, 10, "a", "ACTIVE" );
1 row created.
SQL>
SQL> DROP TABLE emp;
TABLE dropped.
SQL>


Violate an unique constraints

SQL>
SQL> CREATE TABLE person (
  2       person_code VARCHAR2(3) PRIMARY KEY,
  3       first_name  VARCHAR2(15),
  4       last_name   VARCHAR2(20),
  5       hire_date   DATE
  6       );
TABLE created.
SQL>
SQL> ALTER TABLE person
  2  ADD CONSTRAINT person_unique UNIQUE (
  3       first_name,
  4       last_name,
  5       hire_date
  6       );
TABLE altered.
SQL>
SQL>
SQL>
SQL> INSERT INTO person VALUES ("CA", "Chase", "At", "01-FEB-02");
1 row created.
SQL> INSERT INTO person VALUES ("GA", "Chase", "At", "01-FEB-02");
INSERT INTO person VALUES ("GA", "Chase", "At", "01-FEB-02")
*
ERROR at line 1:
ORA-00001: UNIQUE constraint (sqle.PERSON_UNIQUE) violated
 
SQL> INSERT INTO person VALUES ("BB", "Bob", "Bark", "28-FEB-02");
1 row created.
SQL> INSERT INTO person VALUES ("LB", "Laren", "Baby", "01-MAR-02");
1 row created.
SQL> INSERT INTO person VALUES ("LN", "Linda", "Norman", "01-JUN-03");
1 row created.
SQL>
SQL> SELECT * FROM person;
PER FIRST_NAME      LAST_NAME            HIRE_DATE
--- --------------- -------------------- ---------
CA  Chase           At                   01-FEB-02
BB  Bob             Bark                 28-FEB-02
LB  Laren           Baby                 01-MAR-02
LN  Linda           Norman               01-JUN-03
SQL>
SQL> DROP TABLE person;
TABLE dropped.
SQL>
SQL>