1

Table Animal

has following columns:

  1. SR_ID
  2. First_Name
  3. Last_Name
  4. PET

The PET column has a check constraint; hence it only accepts three values DOG, CAT, FISH.

Sample Data

+-------+-------------+-----------+----------+
| SR_ID |  First_Name | Last_Name |   PET    |
+-------+-------------+-----------+----------+
|     1 |  JOHN       |      D.   |   DOG    | 
|     2 |  MIKE       |      S.   |   CAT    |  
|     3 |  MARIO      |      M.   |   CAT    | 
|     4 |  MICHEAL    |      T.   |   CAT    | 
+-------+-------------+-----------+----------+

How would the admin check (via a query) which values are accepted for the column PET?

Some Background

By running the following query:

Select PET, count(PET) from Animal group by PET

I get the following result:

+-----+------------+
| PET | count(PET) |
+-----+------------+
| CAT |          3 |
| DOG |          1 |
+-----+------------+

The admin only gets DOG and CAT values. It is obvious that FISH won't be found since it is not in the database. How can we know that PET column accepts the value FISH?

DESCRIBE Animal would just list column names and its datatype - not the values accepted.

2

You can check the condition of the check constraint from all_constraints or user_constraints view.

SQL> create table animal(
SR_ID number, 
First_Name varchar2(20), 
Last_Name varchar2(20), 
PET varchar2(20) constraint ckpet check (PET in ('DOG', 'CAT', 'FISH'))
);

Table created.

SQL> select constraint_name,search_condition 
from all_constraints
where table_name='ANIMAL'
and constraint_type='C'; 

CONSTRAINT_NAME     SEARCH_CONDITION
---------------     ---------------------------
CKPET                PET in ('DOG', 'CAT', 'FISH')
  • Perfect! It would be great if you could share some reference docs on SEARCH_CONDITION – Ash Apr 6 '17 at 2:02
  • SEARCH_CONDITION is just a column of ALL_CONSTRAINTS view to show search condition for a check constraint`. docs.oracle.com/cd/E18283_01/server.112/e17110/… – JSapkota Apr 6 '17 at 3:42
0

There are three things that can go on in the background:

  1. The column has a check constraint, which lists the allowed values
  2. There's a referential integrity constraint linking that column to another table, which gives the allowed values
  3. A trigger at insert or commit checks the supplied values and disallows some

By taking your favorite tool (SQLdeveloper, TOAD or sqlplus) and looking at the table definition, you can see, if one of the cases is happening.

The easiest way is to issue:

insert into "Animals"(pet) values ('Fish');

And then rollback.

  • Thank you for the answer. and sorry to say that its not clear. comments on your 3 points. 1. Yes the column has "check constraint" Hence it only accepts three values DOG, CAT, FISH 2. Could you please elaborate more.? with sample query? you mentioned to use insert into "Animals"(pet) values ('Fish'); but the admin is not aware that the column accepts the value "Fish" Appreciate your response on this. Thank you – Ash Mar 31 '17 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.