Oracle 空和唯一约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5860388/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle null and unique constraint
提问by palAlaa
Does a unique constraint include a not null constraint?
唯一约束是否包括非空约束？
I have a case that one attribute cellPhonecan be NULLbut cannot be repeated, so I give it 2 constraints: "not null" and "unique", in a case of updating the record, if user didn't enter a value I put 0 in the field, so it makes this exception:
我有一个属性cellPhone可以NULL但不能重复的情况，所以我给它两个约束：“not null”和“unique”，在更新记录的情况下，如果用户没有输入一个值，我把 0字段，所以它使这个例外：
SEVERE: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (TEST1.OSQS_PARENTS_CELLPHONE_UK) violated
What should I do in the UPDATEcase?
遇到这种UPDATE情况我该怎么办？
EDIThere's the definition of table ddl
编辑这里是表 ddl 的定义
CREATE TABLE "TEST1"."OSQS_PARENTS"
( "PARENT_NO" NUMBER(38,0),
"PARENT_NAME" VARCHAR2(4000 BYTE),
"PARENT_ID" NUMBER(38,0),
"PARENT_EMAIL" VARCHAR2(30 BYTE),
"PARENT_CELLPHONE" NUMBER(38,0)
)
and here's an image of the constraints
这是约束的图像
and here is the update statement
这是更新声明
Parent aParent; //is an object I pass through a function
String SQlUpdate = "UPDATE OSQS_PARENTS P SET P.PARENT_ID=?,P.PARENT_EMAIL=?,P.PARENT_CELLPHONE=?"
+ " where P.PARENT_NO=?";
PreparedStatement pstmt = null;
try {
pstmt = con.prepareStatement(SQlUpdate);
pstmt.setLong(1, aParent.getId());
pstmt.setString(2, aParent.getEmail());
pstmt.setLong(3, aParent.getCellPhoneNo());
pstmt.setLong(4, parentNo);
pstmt.executeUpdate();
}
回答by Harrison
it sounds like this:
听起来像这样：
cellPhone must be unique. When user does not input value, you mark it as a 0. Thus it fails when you try to insert multiple 0 values into a 'UNIQUE' column.
手机必须是唯一的。当用户没有输入值时，您将其标记为 0。因此，当您尝试将多个 0 值插入到“UNIQUE”列中时它会失败。
I believe you need to drop the NOT NULL constraint on the column (allow it to be UNIQUE yes, but allow NULLS).
我相信您需要删除列上的 NOT NULL 约束（允许它是 UNIQUE 是，但允许 NULLS）。
Then when user inputs no value, use it as a NO value (unknown = null <> 0 -- 0 is a known value )
然后当用户没有输入值时，将其用作 NO 值（unknown = null <> 0 -- 0 is a known value）
throw an IF into your statement, if value then what you have, otherwise SET IT TO NULL!\
将 IF 放入您的语句中，如果值则为您拥有的值，否则将其设置为 NULL！\
pstmt.setNull(3, java.sql.Types.INTEGER);
