唯一约束和NULL值 [英] Unique constraint and NULL values
问题描述
我想在列上强制执行这样的约束，以确保
值都是唯一的，但这不适用于NULL值。 （即那里
可能在列中有多个NULL值。）
我怎样才能实现这个目标？
我想我会得到最讨厌的表/视图正在改变，
触发器/功能可能看不到它错误，如果我试图编写一个触发器，
检查插入/更新时非空值的唯一性。
I want to enforce such a constraint on a column that would ensure that the
values be all unique, but this wouldn''t apply to NULL values. (I.e. there
may be more than one NULL value in the column.)
How can I achieve this?
I suppose I would get the most-hated "table/view is changing,
trigger/function may not see it" error if I tried to write a trigger that
checks the uniqueness of non-null values upon insert/update.
推荐答案
" Agoston Bejo" <顾*** @ freemail.hu>在消息中写道
news：cl ********** @ news.caesar.elte.hu ...
|我想在一个确保
|的列上强制执行这样的约束值都是唯一的，但这不适用于NULL值。 （即。
|列中可能有多个NULL值。）
|我怎样才能实现这个目标？
|我想我会得到最讨厌的表/视图正在改变，
|触发器/功能可能看不到它错误，如果我试图写一个触发器
|在插入/更新时检查非空值的唯一性。
|
|
你是否尝试过标准的UNIQUE约束在列上？
与SQL-Server不同（除非他们自上次工作以来它们已经改变了），
Oracle正确处理空值这个场景（即一个NULL值
永远不会被认为等于另一个NULL值）
++ mcs
"Agoston Bejo" <gu***@freemail.hu> wrote in message
news:cl**********@news.caesar.elte.hu...
| I want to enforce such a constraint on a column that would ensure that the
| values be all unique, but this wouldn''t apply to NULL values. (I.e. there
| may be more than one NULL value in the column.)
| How can I achieve this?
| I suppose I would get the most-hated "table/view is changing,
| trigger/function may not see it" error if I tried to write a trigger that
| checks the uniqueness of non-null values upon insert/update.
|
|
did you try a standard UNIQUE constraint on the column?
unlike SQL-Server (unless they''ve changed it since I last worked on it),
Oracle processes null values properly in this scenario (i.e., one NULL value
is never consider equal to another NULL value)
++ mcs
" Mark C. Stock" < mcstockX @ Xenquery .com>写道：
"Mark C. Stock" <mcstockX@Xenquery .com> wrote:
Agoston Bejo <顾*** @ freemail.hu>在消息中写道
新闻：cl ********** @ news.caesar.elte.hu ...
|我想在一个确保
|的列上强制执行这样的约束值都是唯一的，但这不适用于NULL值。 （即，
|列中可能有多个NULL值。）
|我怎样才能做到这一点？
|我想我会得到最讨厌的表/视图正在改变，
|触发器/功能可能看不到它如果我试图写一个触发器
|会出错在插入/更新时检查非空值的唯一性。
|
|
您是否在列上尝试了标准的UNIQUE约束？
与SQL-Server不同（除非他们自上次处理以来它们已经改变了），在这种情况下Oracle正确处理空值（即，一个NULL值从未被视为等于另一个NULL值） ）
++ mcs
"Agoston Bejo" <gu***@freemail.hu> wrote in message
news:cl**********@news.caesar.elte.hu...
| I want to enforce such a constraint on a column that would ensure that the
| values be all unique, but this wouldn''t apply to NULL values. (I.e. there
| may be more than one NULL value in the column.)
| How can I achieve this?
| I suppose I would get the most-hated "table/view is changing,
| trigger/function may not see it" error if I tried to write a trigger that
| checks the uniqueness of non-null values upon insert/update.
|
|
did you try a standard UNIQUE constraint on the column?
unlike SQL-Server (unless they''ve changed it since I last worked on it),
Oracle processes null values properly in this scenario (i.e., one NULL value
is never consider equal to another NULL value)
++ mcs
是的。唯一索引可以强制执行唯一约束并且可以处理NULL ..另一方面，主键是不能拥有
NULLs ..
Yep..A Unique index can enforce a Unique Constraint and can handle NULLs ..A Primary key, on the other hand, cannot have
NULLs..
2004年10月21日星期四09:37:42 -0400 schrieb Mark C. Stock
< mcstockX @ Xenquery .com>：
Am Thu, 21 Oct 2004 09:37:42 -0400 schrieb Mark C. Stock
<mcstockX@Xenquery .com>:
您是否在列上尝试了标准的UNIQUE约束？
与SQL-Server不同（除非他们上次使用它后它们已经改变了），否则Oracle在这种情况下正确处理空值（即，一个NULL值
永远不会考虑等于另一个NULL值）
是的，但这是常规问题，因为NULL<> NULL"评估
为null，因此也不是。所以两个空值永远不应该被认为是不同的。
++ mcs
did you try a standard UNIQUE constraint on the column?
unlike SQL-Server (unless they''ve changed it since I last worked on it),
Oracle processes null values properly in this scenario (i.e., one NULL
value
is never consider equal to another NULL value)
Yes, but it''s a matter of convention because "NULL <> NULL" evaluates
to null and thus is also not true. So two null values should never
be considered different.
++ mcs
- -
Frank Piron，
etfrankatkonaddotn
（leftrotate two）
--
Frank Piron,
etfrankatkonaddotn
(leftrotate two)
这篇关于唯一约束和NULL值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
