oracle 如何在oracle上添加主键约束？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/43852094/
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 add primary key constraint on oracle?
提问by Srabanti Das
CREATE TABLE buses(Bus_no NUMBER(11) NOT NULL ,Bus_name Varchar2(20),Type VARCHAR2(20),Total_seats Number(11),Avail_seats Number(11));
ALTER TABLE buses
ADD CONSTRAINT PK_BUSES UNIQUE(Bus_no);
This is the table I need to create .. and this should be the output:
这是我需要创建的表 .. 这应该是输出：
Name Null? Type
----------------------------------------- -------- ----------------------------
BUS_NO NOT NULL NUMBER(11)
BUS_NAME VARCHAR2(20)
TYPE VARCHAR2(20)
TOTAL_SEATS NUMBER(11)
AVAIL_SEATS NUMBER(11)
CONSTRAINT_NAME
------------------------------
PK_BUSES
However I am getting this extra line SYS-C00403053 along with my output table,please help to remove this.
但是，我收到了这条额外的 SYS-C00403053 行以及我的输出表，请帮助删除它。
CONSTRAINT_NAME
------------------------------
SYS_C00403053
PK_BUSES
回答by Alex Poole
You haven't added a primary key, you've added a unique constraint. While a unique constraint and a not-null constraint are effectively the same, they are not actually the same as an actual primary key.
您尚未添加主键，而是添加了唯一约束。虽然唯一约束和非空约束实际上是相同的，但它们实际上与实际的主键不同。
As @GurwinderSingh said, the SYS_C00403053is a system-generated name for the not-null constraint. It is possible, but unusual, to name a not-null constraint:
正如@GurwinderSingh 所说，这SYS_C00403053是非空约束的系统生成名称。命名非空约束是可能的，但不寻常：
-- just to clean up what you have in the question, remove the unique constraint
ALTER TABLE buses DROP CONSTRAINT PK_BUSES;
ALTER TABLE buses MODIFY Bus_no NULL;
ALTER TABLE buses MODIFY Bus_no CONSTRAINT BUS_NO_NOT_NULL NOT NULL;
desc buses
Name Null? Type
----------- -------- ------------
BUS_NO NOT NULL NUMBER(11)
BUS_NAME VARCHAR2(20)
TYPE VARCHAR2(20)
TOTAL_SEATS NUMBER(11)
AVAIL_SEATS NUMBER(11)
select constraint_name, constraint_type, search_condition
from user_constraints where table_name = 'BUSES';
CONSTRAINT_NAME C SEARCH_CONDITION
------------------------------ - --------------------------------------------------------------------------------
BUS_NO_NOT_NULL C "BUS_NO" IS NOT NULL
But as you want a primary key anyway, you can drop the separate not-null check, as it's implied by a (proper) primary key:
但是无论如何你想要一个主键，你可以删除单独的非空检查，因为它是由（正确的）主键暗示的：
ALTER TABLE buses MODIFY Bus_no NULL;
ALTER TABLE buses ADD CONSTRAINT PK_BUSES PRIMARY KEY (Bus_no);
desc buses
Name Null? Type
----------- -------- ------------
BUS_NO NOT NULL NUMBER(11)
BUS_NAME VARCHAR2(20)
TYPE VARCHAR2(20)
TOTAL_SEATS NUMBER(11)
AVAIL_SEATS NUMBER(11)
select constraint_name, constraint_type, search_condition
from user_constraints where table_name = 'BUSES';
CONSTRAINT_NAME C SEARCH_CONDITION
------------------------------ - --------------------------------------------------------------------------------
PK_BUSES P
You now only see the primary key constraint listed, but the column is still marked as not nullable, and you get the same error if you try to insert null:
您现在只能看到列出的主键约束，但该列仍被标记为不可为空，并且如果您尝试插入空值，则会出现相同的错误：
insert into buses (bus_no) values (null);
ORA-01400: cannot insert NULL into ("MY_SCHEMA"."BUSES"."BUS_NO")
回答by Gurwinder Singh
SYS_C00403053is the system generated name given to the NOT NULLconstraint on Bus_nocolumn. Your result is as expected only.
SYS_C00403053是系统生成的名称给列上的NOT NULL约束Bus_no。您的结果仅符合预期。
