Oracle SQL 使用空字符串更新 NOT NULL 列

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/19211419/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-19 01:59:12  来源:igfitidea点击:

Oracle SQL Updating a NOT NULL column with an empty string

sqloracle

提问by John McDonnell

I am having an issue writing an SQL Update statement, where I need to update a not null field with an empty string.

我在编写 SQL 更新语句时遇到问题,我需要用空字符串更新非空字段。

UPDATE channel_mgmt.channels
    SET registered_address_id=p_address_id
        ,vendor_id=p_spc_code
     WHERE id=v_channel_id;

In this case, p_spc_code, can be set to '', and I am running into a SQL error when I run this:

在这种情况下,p_spc_code 可以设置为 '',当我运行这个时,我遇到了 SQL 错误:

Error report:
ORA-01407: cannot update ("CHANNEL_MGMT"."CHANNELS"."VENDOR_ID") to NULL
ORA-06512: at line 8973
 01407. 00000 -  "cannot update (%s) to NULL"
*Cause:    
*Action:

Any ideas how I can get around this? I need to use the empty string some cases, but I am unsure why oracle is complaining about a null value.

有什么想法可以解决这个问题吗?在某些情况下我需要使用空字符串,但我不确定 oracle 为什么会抱怨空值。

desc channel_mgmt.channels
Name                  Null     Type               
--------------------- -------- ------------------ 
ID                    NOT NULL NUMBER(16)         
CHANNEL_STATUS_KEY    NOT NULL VARCHAR2(64 CHAR)  
REGISTERED_ADDRESS_ID NOT NULL NUMBER(16)         
VENDOR_ID             NOT NULL VARCHAR2(64 CHAR)

回答by Nick Krasnov

If there is a NOT NULLconstraint defined on a column you cannot update the table by specifying nullor zero length string('') as a new value for a column. Oracle treats zero length string(''when assigned to a varchar2column or variable) as NULL, so from the oracle's point of view, these two statements are the same set col_name = nulland set col_name = ''. If you want to allow for a column to accept NULLs you need to remove not null constraint from that column if it won do any harm:

如果NOT NULL在列上定义了约束,则无法通过将null或 零长度 string( '')指定为列的新值来更新表。Oracle 将零长度字符串(''当分配给varchar2列或变量时)视为NULL,因此从oracle 的角度来看,这两个语句是相同的set col_name = nullset col_name = ''。如果要允许列接受NULLs,则需要从该列中删除 not null 约束(如果它会造成任何伤害):

alter table <<table_name>> modify (<<column_name>> [<<data type>>] null)

回答by veljkoz

Have you tried using ' '(string with one space in it)? If varchar, than I believe comparisons (for most cases, i.e. you're not specifying to compare white chars) work as expected.

你试过使用' '(string with a space in it) 吗?如果是 varchar,我相信比较(在大多数情况下,即您没有指定比较白字符)按预期工作。

What I mean that ' ' = ' '(single and double whitespace) should evaluate to truefor varcharfields.

我的意思是' ' = ' '(单,双空格)应该求truevarchar领域。

(I don't have an instance of Oracle so I can't be sure...)

(我没有 Oracle 实例,所以我不能确定......)

回答by Walter Mitty

Oracle can't distinguish between and empty string and a null value. It uses the same marker (one byte of zeroes) for both of them.

Oracle 无法区分空字符串和空值。它对它们使用相同的标记(一个字节的零)。

回答by Dom84

If you have a NOT NULL column and your going to update it NULL, maybe you need to think about your logic in your application. Otherwise, you could define a nvl(column, some_pattern) for example '???' (depending on the column's length, of course) for unknown empty Data, when you know your not allowed to insert / update NULL.

如果您有一个 NOT NULL 列并且您打算将其更新为 NULL,那么您可能需要考虑应用程序中的逻辑。否则,您可以定义一个 nvl(column, some_pattern) 例如 '???' (当然取决于列的长度)对于未知的空数据,当您知道不允许插入/更新 NULL 时。

Than you have something to check for to see if something is behaving not correctly or to filter that three questionmarks in a view or something and display it as NULL again.

比你有什么要检查的东西,看看是否有什么行为不正确,或者过滤视图中的三个问号或其他东西,然后再次将其显示为 NULL。

回答by Hamidreza

your id is not null and you want to make it null you can insert all your table rows into another table as backup and then drop your table and create it again with null able id row and insert your rows again from backup just care if you use your id as your primary key it will be not null again

您的 id 不为空,并且您想将其设为空,您可以将所有表行作为备份插入另一个表中,然后删除您的表并使用可以为空的 id 行再次创建它,然后从备份中再次插入您的行,只要关心您是否使用你的 id 作为你的主键,它不会再次为空

another way is use ' ' as in your update and it returns just space but it is not null

另一种方法是在更新中使用 ' ',它只返回空间但不为空

CREATE TABLE BACKUP(
ID                    NOT NULL NUMBER(16),         
CHANNEL_STATUS_KEY    NOT NULL VARCHAR2(64 CHAR),  
REGISTERED_ADDRESS_ID NOT NULL NUMBER(16),         
VENDOR_ID             NOT NULL VARCHAR2(64 CHAR));

INSERT INTO BACKUP (SELECT * FROM YourTable);

ALTER TABLE YourTable(
ID                             NUMBER(16),         
CHANNEL_STATUS_KEY    NOT NULL VARCHAR2(64 CHAR),  
REGISTERED_ADDRESS_ID NOT NULL NUMBER(16),         
VENDOR_ID             NOT NULL VARCHAR2(64 CHAR));

INSERT INTO YourTable (SELECT * FROM BACKUP);

and now use your query

现在使用您的查询