Oracle PL/SQL Tutorial/Table/NOT NULL

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

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

Содержание

Add "NOT NULL" constraint to column with NULL value

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>
SQL>
SQL> ALTER TABLE employee
  2  ADD (
  3        ssn       number(9)
  4  );
TABLE altered.
SQL>
SQL> UPDATE employee
  2  SET ssn = 123456789
  3  WHERE id = 1;
1 row updated.
SQL>
SQL>
SQL> UPDATE employee
  2  SET ssn = 234567890
  3  WHERE id = 2;
1 row updated.
SQL>
SQL>
SQL> UPDATE employee
  2  SET ssn = 345678901
  3  WHERE id = 3;
1 row updated.
SQL>
SQL>
SQL> ALTER TABLE employee
  2  MODIFY (
  3    ssn number(9) NOT NULL
  4  );
ALTER TABLE employee
*
ERROR at line 1:
ORA-02296: cannot enable (sqle.) - NULL VALUES found
 
SQL>
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)
 SSN                    NUMBER(9)
SQL>
SQL> DROP TABLE Employee
  2  /
TABLE dropped.
SQL>


Alter a table column to "not null"

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 MODIFY (first_name NOT NULL);
TABLE altered.
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> DROP TABLE Employee
  2  /
TABLE dropped.


Alter not null and null contraints

SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
  2                    ENAME VARCHAR2(10),
  3                    JOB VARCHAR2(9),
  4                    MGR NUMBER(4),
  5                    HIREDATE DATE,
  6                    SAL NUMBER(7, 2),
  7                    COMM NUMBER(7, 2),
  8                    DEPTNO NUMBER(2));
TABLE created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK",    7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD",  "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER",  7839, TO_DATE("2-APR-1981",  "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> ALTER TABLE emp MODIFY deptno NOT NULL;
TABLE altered.
SQL>
SQL>
SQL> ALTER TABLE emp MODIFY deptno NULL;
TABLE altered.
SQL>
SQL>
SQL> DROP TABLE emp cascade constraint;
TABLE dropped.


Change null status of an existing column

SQL>
SQL> CREATE TABLE product (
  2       product_name     VARCHAR2(25) PRIMARY KEY,
  3       product_price    NUMBER(4,2),
  4       quantity_on_hand NUMBER(5,0),
  5       last_stock_date  DATE
  6       );
TABLE created.
SQL>
SQL> INSERT INTO product VALUES ("Product 1", 99,  1,    "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 2", 75,  1000, "15-JAN-02");
1 row created.
SQL> INSERT INTO product VALUES ("Product 3", 50,  100,  "15-JAN-03");
1 row created.
SQL> INSERT INTO product VALUES ("Product 4", 25,  10000, NULL);
1 row created.
SQL> INSERT INTO product VALUES ("Product 5", 9.95,1234, "15-JAN-04");
1 row created.
SQL> INSERT INTO product VALUES ("Product 6", 45,  1,    TO_DATE("December 31, 2008, 11:30 P.M.","Month dd, YYYY, HH:MI P.M."));
1 row created.
SQL>
SQL>
SQL> DESC product;
 Name                                                  NULL?    Type
 ----------------------------------------------------- -------- ------------------------------------
 PRODUCT_NAME                                          NOT NULL VARCHAR2(25)
 PRODUCT_PRICE                                                  NUMBER(4,2)
 QUANTITY_ON_HAND                                               NUMBER(5)
 LAST_STOCK_DATE                                                DATE
SQL>
SQL> ALTER TABLE product ADD data_load_date VARCHAR2(8) NULL;
TABLE altered.
SQL>
SQL>
SQL> ALTER TABLE product MODIFY data_load_date DATE;
TABLE altered.
SQL>
SQL> DESC product;
 Name                                                  NULL?    Type
 ----------------------------------------------------- -------- ------------------------------------
 PRODUCT_NAME                                          NOT NULL VARCHAR2(25)
 PRODUCT_PRICE                                                  NUMBER(4,2)
 QUANTITY_ON_HAND                                               NUMBER(5)
 LAST_STOCK_DATE                                                DATE
 DATA_LOAD_DATE                                                 DATE
SQL>
SQL>
SQL> UPDATE product SET data_load_date = "15-DEC-2003";
6 rows updated.
SQL>
SQL> DESC product;
 Name                                                  NULL?    Type
 ----------------------------------------------------- -------- ------------------------------------
 PRODUCT_NAME                                          NOT NULL VARCHAR2(25)
 PRODUCT_PRICE                                                  NUMBER(4,2)
 QUANTITY_ON_HAND                                               NUMBER(5)
 LAST_STOCK_DATE                                                DATE
 DATA_LOAD_DATE                                                 DATE
SQL>
SQL> ALTER TABLE product MODIFY data_load_date NOT NULL;
TABLE altered.
SQL>
SQL> DESC product;
 Name                                                  NULL?    Type
 ----------------------------------------------------- -------- ------------------------------------
 PRODUCT_NAME                                          NOT NULL VARCHAR2(25)
 PRODUCT_PRICE                                                  NUMBER(4,2)
 QUANTITY_ON_HAND                                               NUMBER(5)
 LAST_STOCK_DATE                                                DATE
 DATA_LOAD_DATE                                        NOT NULL DATE
SQL>
SQL>
SQL>
SQL> DROP TABLE product;
TABLE dropped.
SQL>
SQL>


Not NULL column

SQL>
SQL> CREATE TABLE myTable (
  2       name  VARCHAR2(25) NOT NULL,
  3       price NUMBER(4,2)  NOT NULL,
  4       start_date DATE);
TABLE created.
SQL>
SQL> INSERT INTO myTable VALUES ("Product Name 1", 1, NULL);
1 row created.
SQL>
SQL> SELECT * FROM myTable;
 
NAME                           PRICE START_DAT
------------------------- ---------- ---------
Product Name 1                     1
SQL> DROP TABLE myTable;
TABLE dropped.
SQL>
SQL>


ORA-01400: cannot insert NULL into

SQL>
SQL> CREATE TABLE emp (
  2    emp_id               NUMBER,
  3    ename             VARCHAR2(40),
  4    hire_date        DATE DEFAULT sysdate,
  5    end_date DATE,
  6    rate     NUMBER(5,2),
  7    CONSTRAINT emp_pk    PRIMARY KEY (emp_id)
  8  );
TABLE created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),NULL,169);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),NULL,121);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),NULL,45);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),NULL,100);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70);
1 row created.
SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300);
1 row created.
SQL>
SQL> SET ECHO ON
SQL> INSERT INTO emp (ename) VALUES ("Kyta");
INSERT INTO emp (ename) VALUES ("Kyta")
*
ERROR at line 1:
ORA-01400: cannot INSERT NULL INTO
("sqle"."EMP"."EMP_ID")
 
SQL>
SQL> DROP TABLE emp;
TABLE dropped.