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.

0

2 Answers 2

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')
2
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.

1
  • 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
    – SlashGen
    Mar 31, 2017 at 16:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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