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
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.
Add not null constraint to different columns
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.
alter table dept modify emp_count NOT NULL
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>
Change a column to "not null" with null data in it
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>
Create a SQL collection type that disallows null values
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>
NOT NULL Column Constraints
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>
NOT NULL CONSTRAINT
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>
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.