null constraint on field

Sagaert Johan

unread,

Dec 12, 2007, 6:45:01 PM12/12/07

to

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

--CELKO--

unread,

Dec 12, 2007, 7:42:55 PM12/12/07

to

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.

>> I have a bit field [sic: columns are not fields!] "married_flag" and "marriage_date" I only would like to allow a NULL value [sic: NULL is not a value] if the "married-flag" BIT field [sic] is FALSE [sic: no such thing in SQL]. <<

>> So I would like a recordset [sic: you update tables] update to fail (generate an exception) when "married_flag" is TRUE [sic: no such thing in SQL] and the date is NULL. <<

>> Can I do this on the table definition side .. <<

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
"marriage_date IS NULL" instead.

>> or do I have to check for this in the client app. <<

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.

ML

unread,

Dec 12, 2007, 7:42:01 PM12/12/07

to

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/

Tom Cooper

unread,

Dec 12, 2007, 9:57:51 PM12/12/07

to

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

"Sagaert Johan" <REMOVEs...@hotmail.com> wrote in message
news:eKWqHkRP...@TK2MSFTNGP05.phx.gbl...

Sagaert Johan

unread,

Dec 13, 2007, 3:25:57 AM12/13/07

to

Thanks

Thats what i had in mind.

Johan

"Tom Cooper" <tomc...@comcast.no.spam.please.net> wrote in message
news:eGEEyPTP...@TK2MSFTNGP02.phx.gbl...