Adding to a column 10 options on PLSQL

Question

I want to add column but it should be 10 options to my database(PL/SQL).

My sql query is look like this:

ALTER TABLE mytable
ADD NEWCOL

Do you think that is it work?

Answer 1

I don't have to think, I know it won't work.

SQL> INSERT_INTO MYTABLE
  2  (MYNEW_COL)
  3  VALUES
  4  (1,2,3,4,5,6,7,8,9,10);
INSERT_INTO MYTABLE
*
ERROR at line 1:
ORA-00900: invalid SQL statement

If you want to insert exactly those values, use a row generator:

SQL> insert into mytable (mynew_col)
  2  select level from dual
  3  connect by level <= 10;

10 rows created.

SQL> select * from mytable;

 MYNEW_COL
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

SQL>

Otherwise, discover other valid ways to do that, such as

SQL> insert into mytable (mynew_col)
  2  select 1 from dual union all
  3  select 2 from dual union all
  4  select 3 from dual;

3 rows created.

or

SQL> insert all
  2    into mytable (mynew_col) values (1)
  3    into mytable (mynew_col) values (2)
  4    into mytable (mynew_col) values (3)
  5  select * from dual;

3 rows created.

SQL>

---

[EDIT] Ah, you turned the question upside down. If you want to add a new column and limit number of valid values, then:

SQL> alter table mytable add newcol number;

Table altered.

SQL> alter table mytable add constraint
  2    ch_col check (newcol between 1 and 10);

Table altered.

Testing:

SQL> update mytable set newcol = 0;
update mytable set newcol = 0
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH_COL) violated


SQL> update mytable set newcol = 11;
update mytable set newcol = 11
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.CH_COL) violated


SQL> update mytable set newcol = 2;

16 rows updated.

SQL>