Re: So what's null then if it's not nothing?

In article <ikj1p1l00amd5932e835mbjkq9pcaaeh5e_at_4ax.com>, hugo_at_pe_NO_rFact.in_SPAM_fo says...

> Well, as I said - it's my personal logic. Maybe it's me who's weird. <g>

I think your arguments make sense.

> >I know that, of course. It would be even easier if I *didn't* have to
> >add NOT NULL.
>
> I disagree. I'll give an example. Consider the column Age, a NOT NULL
> constraint, and a constraint that if an age is entered, it has to be at
> least 18. There are four combinations:
> [8<]
> In the current version, nullability of the column is represented in one
> consistend way. In your proposed version, the representation of the
> constraint that forbids nulls takes a completely different form (in
> fact, it changes from explicitly visible to implied only) as soon as a
> CHECK constraint is added to the column. I don't consider this easier.

I also want NOT NULL to be default; it makes a difference. In D4 syntax (where NULL, or "nil" as Dataphor calls is, is disallowed by default):

1. Age : Integer { nil }
2. Age : Integer
3. Age : Integer { nil, 
     constraint PossiblyOldEnough IsNil(value) or value >= 16 }

> >Yes, I know. But that doesn't jive with what database textbooks teach:
> >That constraints are assertions that hold true for all possible values
> >of the database.
>
> Then these textbooks are wrong.

Yes, given that they talk about SQL. If they talk about the relational model in a more abstract manner, what then?

And why are they so consistently "wrong"? The definition they give for constraints is simpler and more intuitive than the double negation that SQL uses, and most textbooks don't talk about 3VL much. Maybe they should. I'd guess the textbooks consider 3VL and its use in constraint handling too complicated to explain properly. Or perhaps the authors aren't aware of the issues. Or perhaps *I'm* the weird one: The only one on the planet that doesn't intuitively understand that if something is NULL, we don't care about the constraint on it anymore. :)

> Interestingly, MS SQL Server has a backward-compatibility setting that
> forces all columns in newly created tables to be NOT NULL, unless an
> explicit NULL "constraint" (not the correct term - it doesn't constrain,
> it removes a constraint) is added.

It could be argued that constraints deal with *values*, and NULL is not a value.

> Most columns in a database should be NOT NULL. If you believe that the
> most common option should be the default, then NOT NULL should be the
> default. But on the other hand - how would you call the "NOT NOT NULL"
> clause?

It's just a property of an attribute, like a default specification.

> It's not a constraint, since it doesn't constrain. It's more a
> "remove default constraint option". There surely are better terms, but
> however you name it - it's a new beast in the SQL stable, whereas NOT
> NULL is just one of the constraints.

NOT NULL is a bit of a strange beast already, having its own syntax. It is not just any old constraint.

-- 
Jon