ALTER TABLE添加一列并且添加 检查 约束

oracle的约束

* 如果某个约束只作用于单独的字段，即可以在字段级定义约束，也可以在表级定义约束，但如果某个约束作用于多个字段，
必须在表级定义约束
* 在定义约束时可以通过constraint关键字为约束命名，如果没有指定，oracle将自动为约束建立默认的名称

定义primary key约束(单个字段）
create table employees (empno number(5) primary key,...)

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> 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> * from emp;      empno ename      job              mgr hiredate         sal       comm     deptno ---------- ---------- --------- ---------- --------- ---------- ---------- ----------       7369 smith      clerk           7902 17-dec-80        800                    20       7499 allen      salesman        7698 20-feb-81       1600        300         30       7521 ward       salesman        7698 22-feb-81       1250        500         30       7566 jones      manager         7839 02-apr-81       2975                    20       7654 martin     salesman        7698 28-sep-81       1250       1400         30       7698 blake      manager         7839 01-may-81       2850                    30       7782 clark      manager         7839 09-jun-81       2450                    10       7788 scott      analyst         7566 09-dec-82       3000                    20       7839 king       president            17-nov-81       5000                    10       7844 turner     salesman        7698 08-sep-81       1500          0         30       7876 adams      clerk           7788 12-jan-83       1100                    20      empno ename      job              mgr hiredate         sal       comm     deptno ---------- ---------- --------- ---------- --------- ---------- ---------- ----------       7900 james      clerk           7698 03-dec-81        950                    30       7902 ford       analyst         7566 03-dec-81       3000                    20       7934 miller     clerk           7782 23-jan-82       1300                    10 14 rows selected. sql> sql> alter table emp   2      add (   3        gender   varchar(10)); table altered. sql> sql> alter table emp   2      add constraint ck_gender   3      check (gender in ('male', 'female')); table altered. sql> sql> update emp set gender = 'male' where mod(empno,2) = 0; 10 rows updated. sql> sql> select * from emp;      empno ename      job              mgr hiredate         sal       comm     deptno gender ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------       7369 smith      clerk           7902 17-dec-80        800                    20       7499 allen      salesman        7698 20-feb-81       1600        300         30       7521 ward       salesman        7698 22-feb-81       1250        500         30       7566 jones      manager         7839 02-apr-81       2975                    20 male       7654 martin     salesman        7698 28-sep-81       1250       1400         30 male       7698 blake      manager         7839 01-may-81       2850                    30 male       7782 clark      manager         7839 09-jun-81       2450                    10 male       7788 scott      analyst         7566 09-dec-82       3000                    20 male       7839 king       president            17-nov-81       5000                    10       7844 turner     salesman        7698 08-sep-81       1500          0         30 male       7876 adams      clerk           7788 12-jan-83       1100                    20 male      empno ename      job              mgr hiredate         sal       comm     deptno gender ---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------       7900 james      clerk           7698 03-dec-81        950                    30 male       7902 ford       analyst         7566 03-dec-81       3000                    20 male       7934 miller     clerk           7782 23-jan-82       1300                    10 male 14 rows selected. sql> sql> update emp set gender = 'female' where mod(empno,2) = 1; 4 rows updated. sql> sql> select ename, job, gender from emp order by gender, ename; ename      job       gender ---------- --------- ---------- allen      salesman  female king       president female smith      clerk     female ward       salesman  female adams      clerk     male blake      manager   male clark      manager   male ford       analyst   male james      clerk     male jones      manager   male martin     salesman  male ename      job       gender ---------- --------- ---------- miller     clerk     male scott      analyst   male turner     salesman  male 14 rows selected. sql> sql> insert into emp (empno, ename, gender) values (8001, 'pat', 'unknown'); insert into emp (empno, ename, gender) values (8001, 'pat', 'unknown') * error at line 1: ora-02290: check constraint (sys.ck_gender) violated sql> sql> drop table emp; table dropped.