null constraint on field - Sagaert Johan

Hi

When designing a table i can allow or disallow null values for fields.
Is there a way i could do this selective ; example

i have a bitfield Maried and DateMaried
i only would like to allow a null value if  the Maried bitfield is false.

So i would like an recordset update to fail (generate an exception) when
maried is true and the date is null.

Can i do this on the table definition side , or do i have to check for this
in the client app.

Johan

null constraint on field - M

Why the bit column? Isn't it enough to either have the date column set or not?
And what if a person is married but the date is unknown?

Anyway, IMO you should implement the same constraint(s) both in the
application as well as the data store.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

null constraint on field - Tom Cooper

You can do it with a table check constraint, for example,

Set NoCount ON
Create Table #Foo
(FooDate datetime Null,
FooValid bit Not Null,
Constraint FooDateMustBeValid
Check (FooDate Is Not Null Or FooValid = 0))
-- These work
Select 'The following inserts work'
Insert #Foo (FooDate, FooValid) Values ('20070101', 0)
Insert #Foo (FooDate, FooValid) Values (Null, 0)
Insert #Foo (FooDate, FooValid) Values ('20070101', 1)
Select * From #Foo
Select 'The following insert gets an error'
Insert #Foo (FooDate, FooValid) Values (Null, 1)
go
Drop Table #Foo

Tom

null constraint on field - Sagaert Johan

Thanks

Thats what i had in mind.

Johan

null constraint on field - --CELKO--

You are writing assembly language with SQL!   You don't know that SQL
does not have a Boolean data type and that the BIT **numeric** data
type is proprietary and non-relational.  You don't know that columns
and fields are totally different concepts.  You violated ISO-11179
rules for data element names, so I renamed them for you.   In short,
everything you are doing is wrong.




Why do you have a flag when you have a date?  SQL uses predicates and
not Booleans to discover the state of the data.   Just look for


NEVER trust applications with data integrity.  That is a basic
principle of RDBMS design.

If you just do not care about doing things right and want to have to
maintain a total nightmare of a schema, then you can write a few
hundred coinstraints like this, to maintain your assembly language
flags:


CHECK (CASE
WHEN married_flag = CAST (1 AS BIT) AND
marriage_date IS NULL
THEN 'F'
WHEN married_flag = CAST (1 AS BIT) AND
marriage_date IS NOT NULL
THEN 'T'
WHEN married_flag = CAST (0 AS BIT) AND
marriage_date IS NULL
THEN 'T'
WHEN married_flag = CAST (0 AS BIT) AND
marriage_date IS NOT NULL
THEN 'T' ELSE  'F' = 'T')

Now if you let the married_flag be NULL-able, things get much worse.