Databases Reference
In-Depth Information
---------------------------- -------- -----------------
JOB_ID NOT NULL VARCHAR2(10)
JOB_TITLE NOT NULL VARCHAR2(35)
MIN_SALARY NUMBER(6)
MAX_SALARY NUMBER(6)
When a new job is added or an existing job is modified, the columns for the job
identifier and the job title must contain a value. The salary range columns, how-
ever, can remain undefined—either explicitly by assigning
NULL
values to them or
statement.
The boss, King, wants to make sure that when a new job is created, a mini-
mum salary is always entered for the job. Janice, the DBA, changes the structure
of the
implicitly by not specifying those two column names in an
INSERT
JOBS
table with the
ALTER
TABLE
command, as follows:
alter table jobs modify (min_salary not null);
Table altered.
The next time someone from HR tries to add a new
JOBS
table row without
a minimum salary, here is what will happen:
insert into jobs (job_id, job_title)
values('IT_DBDES', 'Database Designer');
insert into jobs (job_id, job_title)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into
("HR"."JOBS"."MIN_SALARY")
The
MIN_SALARY
field must be entered with some value, even if it is zero:
insert into jobs (job_id, job_title, min_salary)
values('IT_DBDES', 'Database Designer', 12500);
1 row created.
At some point, the HR department may want to update this row in the
JOBS
table to indicate an upper range for the salary for this job position.
However, it would not be unreasonable to expect that some job positions
may not have any upper value, and therefore a
field could reflect the business rule that there is no maximum salary in force
for a particular position.
NULL
value in the
MAX_SALARY
