New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Unique index allowed on NULLABLE field [CORE427] #771
Comments
Commented by: Alice F. Bird (firebirds) Date: 2003-04-26 09:19 Agreement: from SQL, UNIQUE clause must accept nullable fields. |
Commented by: Alice F. Bird (firebirds) Date: 2001-03-02 06:45 This is the answer from SQL expert Diane Brown:I'll answer the easy part first: the ISO SQL standard says In SQL92, PK constraint is violated if any value is null. By the way, the SQL92 rules for Primary Key constraint and
|
Commented by: Alice F. Bird (firebirds) Date: 2001-02-28 01:29 I'm not sure this is a bug. Its handy to allow a unique constraint on a column that allows nulls. The nice way to handle this is to allow any number of null values in the column but if the value isn't null, then it has to be unique. This is even the correct relational model operation since a null value isn't equal to anything else *even another null*. So each null value is unique! A place this is nice for example is a customer number that is optional but when entered must be unique. Other things like account numbers, login names, ... all can work this way. If you can't use a unique constraint on a column like this, then its really a pain to implement the above. I would argue that the unique constraint should be changed to allow it on nullable fields but if this is against the SQL-92 spec, then at least leave the ability to create unique indexes on null columns! PS: other databases also allow unique indexes on nullable columns (Sybase SQL Anywhere for example) and its a lack of understanding of how nulls actually work in the relational model (ie: the rule that null != anything else) that makes people think this shouldn't be allowed but it should and it is very useful! |
Commented by: Alice F. Bird (firebirds) Date: 2000-11-05 06:55 C. |
Modified by: @pcisarWorkflow: jira [ 10451 ] => Firebird [ 14741 ] |
Modified by: @pavel-zotovQA Status: No test |
Submitted by: Claudio Valderrama C. (robocop)
SFID: 221649#
Submitted By: robocop
I noticed it in 1999 (IB6 in private beta testing) but I'm not sure who discovered it. It still exists and may be rather old.
create table mau(a int);
commit;
create UNIQUE index idx_mau on mau(a);
commit;
Whether this was left as a transition from Paradox (that accepts NULL PKs) or it's an oversight is what I want to confirm.
As a comparation, you cannot declarate a PK or UNIQUE constraint if you don't include the NOT NULL clause on the affected field(s), so the underlying automatic index for such constraint will be always on non-nullable column(s).
C.
The text was updated successfully, but these errors were encountered: