Oracle PL/SQL/Constraints/NOT NULL
Содержание
- 1 Adding constraint that checks values and prohobits nulls
- 2 Add not null constraint to different columns
- 3 alter table dept modify emp_count NOT NULL
- 4 Change a column to "not null" with null data in it
- 5 Create a SQL collection type that disallows null values
- 6 NOT NULL Column Constraints
- 7 NOT NULL CONSTRAINT
- 8 ORA-01400: cannot insert NULL into
Adding constraint that checks values and prohobits nulls
<source lang="sql">
SQL> SQL> CREATE TABLE purchase (
2 product_name VARCHAR2(25), 3 product_price NUMBER(4,2), 4 purchase_date DATE 5 );
Table created. SQL> SQL> ALTER TABLE purchase ADD (
2 CONSTRAINT reasonable_date CHECK( 3 purchase_date IS NOT NULL 4 AND 5 TO_CHAR(purchase_date, "YYYY-MM-DD") >= "2000-06-30" 6 ) 7 );
Table altered. SQL> SQL> INSERT INTO purchase VALUES ("Small Widget", 10, "28-FEB-00"); INSERT INTO purchase VALUES ("Small Widget", 10, "28-FEB-00")
ERROR at line 1: ORA-02290: check constraint (sqle.REASONABLE_DATE) violated
SQL> SQL> SQL> SQL> drop table purchase; Table dropped.
</source>
Add not null constraint to different columns
<source lang="sql">
SQL> create table myTable (
2 id number(8) not null, 3 code number(4) not null, 4 inception date not null, 5 description varchar2(60) not null, 6 price number(6,2) not null, 7 in_stock varchar2(1) 8 );
Table created. SQL> SQL> drop table myTable; Table dropped.
</source>
alter table dept modify emp_count NOT NULL
<source lang="sql">
SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); SQL> SQL> SQL> alter table dept
2 add emp_count number 3 constraint must_be_between_3_8 4 check(emp_count between 3 and 8 OR emp_count = 0) 5 deferrable initially deferred;
SQL> SQL> update dept set emp_count = (select count(*) from emp where emp.deptno = dept.deptno )
2 /
SQL> SQL> alter table dept
2 modify emp_count NOT NULL;
SQL> SQL> create trigger emp_dept_cnt_trigger
2 after insert or update or delete on emp 3 for each row 4 begin 5 if ( updating and :old.deptno = :new.deptno ) 6 then 7 return; 8 end if; 9 if ( inserting or updating ) 10 then 11 update dept set emp_count = emp_count+1 12 where deptno = :new.deptno; 13 end if; 14 if ( updating or deleting ) 15 then 16 update dept set emp_count = emp_count-1 17 where deptno = :old.deptno; 18 end if; 19 end; 20 /
SQL> SQL> drop table emp; SQL> drop table dept; SQL>
</source>
Change a column to "not null" with null data in it
<source lang="sql">
SQL> SQL> CREATE TABLE employees
2 ( employee_id number(10) not null, 3 last_name varchar2(50) not null, 4 email varchar2(30), 5 hire_date date, 6 job_id varchar2(30), 7 department_id number(10), 8 salary number(6), 9 manager_id number(6) 10 );
Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "oracle1@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created. SQL> SQL> select * from employees; EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID
-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
1001 Lawson lawson@g.ru 01-JAN-02 MGR 1 30000 1004
1002 Wells wells@g.ru 01-JAN-02 DBA 2 20000 1005
1003 Bliss bliss@g.ru 01-JAN-02 PROG 3 24000 1004
1004 Kyte tkyte@a.ru 14-JUN-98 MGR 4 25000 1005
1005 Viper sdillon@a .ru 11-JUN-08 PROG 1 20000 1006
1006 Beck clbeck@g.ru 11-JUN-08 PROG 2 20000
1007 Java java01@g.ru 11-JUN-08 PROG 3 20000 1006
1008 Oracle oracle1@g.ru 11-JUN-08 DBA 4 20000 1006
8 rows selected. SQL> SQL> alter table employees
2 add ( 3 ssn number(9) 4 ) 5 /
Table altered. SQL> SQL> update employees
2 set ssn = 123456789 3 where employee_id = 1;
0 rows updated. SQL> SQL> update employees
2 set ssn = 234567890 3 where employee_id = 2;
0 rows updated. SQL> SQL> update employees
2 set ssn = 345678901 3 where employee_id = 3;
0 rows updated. SQL> SQL> alter table employees
2 modify ( 3 ssn number(9) not null 4 );
alter table employees
ERROR at line 1: ORA-02296: cannot enable (SYS.) - null values found
SQL> SQL> drop table employees; Table dropped. SQL> SQL> SQL>
</source>
Create a SQL collection type that disallows null values
<source lang="sql">
SQL> SQL> CREATE OR REPLACE TYPE integer_varray
2 AS VARRAY(100) OF INTEGER NOT NULL; 3 /
Type created. SQL> SQL> DECLARE
2 intArray INTEGER_VARRAY := integer_varray();
3 BEGIN
4 FOR i IN 1..intArray.LIMIT LOOP
5 intArray.EXTEND;
6 END LOOP;
7 dbms_output.put_line("["||intArray.COUNT||"]");
8 END;
9 /
[100] PL/SQL procedure successfully completed. SQL>
</source>
NOT NULL Column Constraints
<source lang="sql">
SQL> SQL> create table people(
2 employee_id number(9), 3 first_name varchar2(15), 4 last_name varchar2(20), 5 email varchar2(25), 6 constraint pk_people primary key (employee_id) 7 );
Table created. SQL> SQL> insert into people values (1, "T", "Kyte", "YourName@q.ru"); 1 row created. SQL> SQL> insert into people values (2, "S", "Viper", "sdillon@q.ru"); 1 row created. SQL> SQL> insert into people values (3, "C", "Beck", "clbeck@q.ru"); 1 row created. SQL> SQL> commit; Commit complete. SQL> SQL> select * from people; EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL
--------------- -------------------- -------------------------
1 T Kyte YourName@q.ru
2 S Viper sdillon@q.ru
3 C Beck clbeck@q.ru
SQL> SQL> SQL> alter table people
2 add ( 3 ssn number(9) 4 );
Table altered. SQL> SQL> update people set ssn = 123456789 where employee_id = 1; 1 row updated. SQL> SQL> update people set ssn = 234567890 where employee_id = 2; 1 row updated. SQL> SQL> update people set ssn = 345678901 where employee_id = 3; 1 row updated. SQL> SQL> alter table people
2 modify ( 3 ssn number(9) not null 4 );
Table altered. SQL> SQL> desc people;
Name Null? Type ----------------------------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------------------- EMPLOYEE_ID NOT NULL NUMBER(9) FIRST_NAME VARCHAR2(15) LAST_NAME VARCHAR2(20) EMAIL VARCHAR2(25) SSN NOT NULL NUMBER(9)
SQL> SQL> drop table people; Table dropped. SQL> SQL>
</source>
NOT NULL CONSTRAINT
<source lang="sql">
SQL> SQL> CREATE TABLE person (
2 person_code VARCHAR2(3), 3 first_name VARCHAR2(15), 4 last_name VARCHAR2(20), 5 hire_date DATE 6 );
Table created. SQL> SQL> SQL> SQL> ALTER TABLE person MODIFY (first_name NOT NULL); Table altered. SQL> ALTER TABLE person MODIFY (last_name NOT NULL); Table altered. SQL> SQL> INSERT INTO person VALUES ("XL", "Xaviera", NULL, "15-NOV-03"); INSERT INTO person VALUES ("XL", "Xaviera", NULL, "15-NOV-03")
*
ERROR at line 1: ORA-01400: cannot insert NULL into ("sqle"."PERSON"."LAST_NAME")
SQL> SQL> drop table person; Table dropped. SQL> SQL>
</source>
ORA-01400: cannot insert NULL into
<source lang="sql">
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.
</source>
