System property for NULL uniqueness?

[Nils Kilden-Pedersen]

Would that be possible? I need NULL to be considered unique, like it
is in certain compatability modes.

Thanks

[Thomas Mueller]

Hi,

> Would that be possible? I need NULL to be considered unique, like it
> is in certain compatability modes.

Sooner or later, I like to get rid of the system properties. Why do
you need this feature, if not for compatibility with another database?
And if for compatibility, why not use the compatibility mode?

Regards,
Thomas

[Nils Kilden-Pedersen]

On Nov 1, 11:25 am, Thomas Mueller <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> > Would that be possible? I need NULL to be considered unique, like it
> > is in certain compatability modes.
>
> Sooner or later, I like to get rid of the system properties. Why do
> you need this feature,

Because that's the behavior I desire for the given schema.

> if not for compatibility with another database?

I use H2 exclusively.

> And if for compatibility, why not use the compatibility mode?

Compatibility mode brings me other properties of a given engine that
is undesirable.

I was under the impression that all these varying interpretations of
the SQL spec would be accessible from system properties, or some other
means of setting them independently of each other. I would hate to see
it go the other way.

[Thomas Mueller]

Hi,

>>> I need NULL to be considered unique

>> Why do you need this feature,
> Because that's the behavior I desire for the given schema.

Any why exactly?

> I use H2 exclusively.

> I was under the impression that all these varying interpretations of
> the SQL spec would be accessible from system properties, or some other
> means of setting them independently of each other.

Many system properties are temporary, and I like to remove them sooner
or later.

Compatibility with other database is important, specially for testing.
However in your case it doesn't sound like the problem is
compatibility. I don't want to bloat the code unnecessarily. H2 should
be standard compliant, but otherwise it should be as simple as
possible.

Regards,
Thomas

[Thomas Mueller]

Hi,

Also, there can be only one system property setting per VM. If
multiple applications use H2 at the same time, and the applications
need different values, it would fail. The only option would be to
support the setting using a SQL statement. But I don't want to do that
(again, code bloat).

Regards,
Thomas

[abc6587]

Are you talking about, for instance, a nullable foreign key? I'd love to have that. It would closely model my OO structure, where an object can point to another object or null. Today, to the best of my knowledge, the best alternative is to drop foreign key constraint altogether to allow the column to be nullable?

Sorry for a hijack if we are talking apples and oranges.

[Jesse Long]

abc6587 wrote:
> Are you talking about, for instance, a nullable foreign key? I'd love
> to have that. It would closely model my OO structure, where an object
> can point to another object or null. Today, to the best of my
> knowledge, the best alternative is to drop foreign key constraint
> altogether to allow the column to be nullable?
>
> Sorry for a hijack if we are talking apples and oranges.

Hi,

Already exists. Just make the foreign key column nullable. No need to
drop foreign key constraint.

CREATE TABLE COLORS (ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, NAME
VARCHAR NOT NULL UNIQUE);
CREATE TABLE THING (NAME VARCHAR NOT NULL PRIMARY KEY, COLOR_ID INT /*
no NOT NULL here */ REFERENCES COLORS(ID));
INSERT INTO THING (NAME) VALUES ('Air');
SELECT * FROM THING;
NAME COLOR_ID
Air null

HTH,
Jesse