Alter table to add a column and then add 'check' constraint to it : Add Column « Table « Oracle PL / SQL






Alter table to add a column and then add 'check' constraint to it

   
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> select * 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.

   
    
  








Related examples in the same category

1.Add a char type column to a table
2.Add a date type column to a table
3.Alter table: add a column to an existing table
4.Add two columns to a table
5.Alter a table to add two columns and use select to check
6.Alter table to add two columns and then drop them
7.Alter table to add a column and then add constraint to it
8.Alter table to add a primary key
9.Alter table to add varray type column
10.Alter table to add date type column
11.Add more columns
12.Add new column with default value and not null