Q

SQL CHECK constraints to validate table inserts

I have various validation rules to be applied while inserting the records in a table. For example, if the type is 'Tool' the subtype can only be 'Template' or 'Document.' How can I achieve this?

I have this table:

MyTable
-------
type    varchar2(20)
subtype varchar2(40)

I have various validation rules to be applied while inserting the records in this table. For example, if the type is 'Tool' the subtype can only be 'Template' or 'Document.' If the type is 'Role' the subtype can only be 'Group' or 'Individual.' How can I achieve this?

The standard SQL approach here would be the good old CHECK constraint:

create table MyTable
( type    varchar2(20)
, subtype varchar2(40)
, constraint valid_types
    check ( 
      type in ('Tool','Role') 
          )
, constraint valid_subtypes
    check ( 
      case when type = 'Tool'
            and subtype in ('Template'
                           ,'Document')
           then 1
           when type = 'Role'
            and subtype in ('Group'
                           ,'Individual')
           then 1
           else 0 end = 1
          )
)

Disclaimer: VARCHAR2 suggests that you are using Oracle. The above CHECK constraints have not been tested in Oracle. We will surely hear immediately from one of our readers if in fact Oracle does not support these constraints.

This was first published in June 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close