oracle 如何删除SQL中的非空约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31281870/
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
How to delete Not null constraint in SQL?
提问by Ram BM
I just created a table like below:
我刚刚创建了一个如下表：
CREATE TABLE EMP
(
ENO NUMBER(5, 0) not null,
ENAME VARCHAR2(20 BYTE),
SAl NUMBER(10, 0),
DPTNAME VARCHAR2(50 BYTE),
EPLACE VARCHAR2(20 BYTE),
DOB DATE
);
Now I want to disable that NOT NULLconstraint. I tried with ALTER TABLE EMP MODIFY (ENO NOT NULL DISABLE);, but it showing some error.
现在我想禁用那个NOT NULL约束。我尝试过ALTER TABLE EMP MODIFY (ENO NOT NULL DISABLE);，但它显示了一些错误。
Could you please suggest me where I'm going wrong?
你能告诉我我哪里出错了吗？
采纳答案by Sachu
Try
尝试
ALTER TABLE EMP MODIFY (ENO null);
回答by Ed Gibbs
You can drop the constraint using Sachu's answer (which BTW I don't think deserved a downvote).
您可以使用 Sachu 的回答来取消约束（顺便说一句，我认为这不应该被否决）。
To disable the constraint you first need to find its name, which is generated by Oracle. You can find the name in Oracle's USER_CONSTRAINTSview: look for the one where the "search condition" is "ENO" IS NOT NULL" -- in your question it will be the only constraint in the table but in other cases there may be multiple constraints on the table (or even on the column).
要禁用约束，您首先需要找到它的名称，该名称由 Oracle 生成。您可以在 Oracle 的USER_CONSTRAINTS视图中找到名称：查找“搜索条件”为"ENO" IS NOT NULL“的那个”——在您的问题中，它将是表中的唯一约束，但在其他情况下，表上可能有多个约束（甚至列上）。
SQL> CREATE TABLE EMP
2 (
3 ENO NUMBER(5, 0) not null,
4 ENAME VARCHAR2(20 BYTE),
5 SAl NUMBER(10, 0),
6 DPTNAME VARCHAR2(50 BYTE),
7 EPLACE VARCHAR2(20 BYTE),
8 DOB DATE
9 );
Table created.
SQL> SELECT CONSTRAINT_NAME, SEARCH_CONDITION
2 FROM USER_CONSTRAINTS
3 WHERE TABLE_NAME = 'EMP';
CONSTRAINT_NAME SEARCH_CONDITION
--------------- -----------------
SYS_C009208 "ENO" IS NOT NULL
So the name Oracle gave the constraint was SYS_C009208. Now you can disable it:
所以 Oracle 给约束的名称是SYS_C009208. 现在您可以禁用它：
SQL> ALTER TABLE EMP DISABLE CONSTRAINT SYS_C009208;
Table altered.
