In oracle unique key field accept how many null value

An interesting workaround using unique fields Unique fields in SQL Server are created using unique constraints or unique indexes, furthermore, each unique constraint uses a unique index. Subscribe for more articles Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed. Simple Talk. Rate this article. The Oracle version I'm currently using or to be more exact forced to use is 8.

Maybe in later versions this was corrected, I don't know. Here, when I tried it, it worked the way I described in my original post. David Portas. DA Morgan. So considering them the same or different is irrelevant. Would you consider them the same? Unfortunately Microsoft disagrees ;- Actually that isn't entirely true.

It just isn't the default. Thu, 21 Oct Mark C. IMO the most plausible argument for the Oracle approach is the observation that allowing exactly one row with a null value in a certain column would mean that 1 The column value is "identifying" the row unique constraint. There are some workarounds but no supported configuration parameter for changing the unique constraint behaviour. I would be very surprised if there is such an undocumented feature but if you know differently can you post an example or a reference?

Unlike true and false, NULL acts on both levels because it can be a column's "value" and can be the result of a logical expression. I've loaned my copy of the book to another instructor until he gets his own so I can't quote the page and paragraph.

If Kyte says it is there I'm inclined toward believing that it is, indeed, there. The difference you point to is clear for me. But i did not find a real world example for a column which is unique in a table and at most one row with a NULL value for this column should be allowed.

If you want to get around this, then use a function-based index to avoid including in a unique index any rows where either of the columns is null. One method would be Expand Select Wrap Line Numbers. It seems to me that this should not be a violation of the uniqueness i. I know the database won't, but I don't understand the reasoning. Why the one-null-per-column requirement? Paraphrasing Frank Piron above , it seems that Oracle is treating null as an identifying value, which doesn't make sense to me.

Hans Wijte. He doesn't suggest that this is an undocumented feature or that such a variable really exists, but that this is solely due to a difference between Oracle and SQL Server or Sybase in the handling of null values Hans Wijte.

I have a rationale. Question and Answer. You Asked I have a table with 4 columns which would uniquely identify a row. January 11, - am UTC. I am personally not a fan of using a column for two things -- just my 2 cents.

It'll cause massive confusion down stream. Design flaw? Kevin, January 11, - pm UTC. This looks like a case where two different relationships are being crammed into a single table. R1 is a relation a,b,c,e and R2 is a relation a,b,d,e. You don't have to worry about the convoluted "primary keys with nulls" workarounds - you've got pure primary keys right there. The constraint "only one of c and d is populated" is implemented by definition. The only major problem is if a foreign key needed to reference all four columns a, b, c, d January 11, - pm UTC.