Constraints

• ehlinger

  Hall of Fame

  Points: 3585

  More actions

  August 24, 2005 at 3:53 pm

  #94705

  I'm new at trying to apply constraints to our database so maybe this is a basic question but...

  I have two fields in a table called temp_code char(1) and exp_date date/time.  Is there a way that I can put a constraint on temp_code that if it is = 'Y' then exp_date cannot be null?  The only valid entries for temp_code are 'Y' and 'N'

• Merrill Aldrich

  SSCrazy

  Points: 2358

  More actions

  August 24, 2005 at 4:16 pm

  #584829

  Yes, using a table level constraint:

  -- With this table:

  create table test2 (

  YesNo char(1) check (YesNo in ( 'Y', 'N')),

  otherStuff char(10) null,

  constraint nullSometimes check ( ( YesNo = 'Y' and not(otherStuff is null) ) or YesNo = 'N')

  )

  --These are allowed:

  insert test2 select 'Y', 'Something'

  insert test2 select 'N', null

  insert test2 select 'N', 'Something'

  --These are not:

  insert test2 select 'F', 'Something'

  insert test2 select 'Y', null

• ehlinger

  Hall of Fame

  Points: 3585

  More actions

  August 25, 2005 at 2:58 pm

  #585188

  Thanks for the help Merrill.