oracle 如何使用空字符串具有空值的主键？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2423507/
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 have a primary key with null values using empty string?
提问by Ram
I have a table in which I need both the values to be primary because I am referencing this combination as foreign key in the other tables. Table definition and the data I need to put are as follows
我有一个表，其中我需要两个值都是主值，因为我在其他表中将此组合引用为外键。表定义和我需要放的数据如下
create table T1
(
sno number(10),
desc varchar2(10),
constraint T1_PK primary key(sno,desc)
)
DATA to put
sno | desc
---------------------------
100 | "hundred"
000 | null
120 | "one twenty"
123 | "" <EMPTY STRING>
000 | "" <EMPTY STRING>
Problem here is desc can be sometimes be null. Primary key can not be null so when I encounter a null value - I'm inserting simply "" in the table. The problem here is some times desc may have empty string. If I insert the about data 100,Null and 100,"" are two different things but I am not able to put them in the table. I don't want to put some string like 'EMPTY' if null because it may confuse the end user who is looking at the table.
这里的问题是 desc 有时可能为空。主键不能为空，所以当我遇到空值时 - 我只是在表中插入“”。这里的问题是有时 desc 可能有空字符串。如果我插入关于数据 100,Null 和 100,"" 是两个不同的东西，但我无法将它们放入表中。如果为空，我不想放一些像 'EMPTY' 这样的字符串，因为它可能会使正在查看表格的最终用户感到困惑。
1) How can I handle the null case for desc, while having it as a primary key. I can not use Auto sequence number. 2) How can I distinguish between null string introduced by me and one that is already there?
1) 如何处理 desc 的空情况，同时将其作为主键。我不能使用自动序列号。2）如何区分我引入的空字符串和已经存在的空字符串？
回答by Aurril
Why don't you introduce a real primary key attribute (like id) which is auto incremented and create an index over sno and desc?
为什么不引入一个真正的主键属性（如 id），它会自动递增并在 sno 和 desc 上创建索引？
回答by cletus
Oracle treats empty VARCHARfields as synonymous to NULL, which is somewhat controversial but it is something you have to live with. So you can't put an empty string into a primary key because you can't put a NULLinto a primary key.
Oracle 将空VARCHAR字段视为 的同义词NULL，这有点争议，但您必须接受它。因此，您不能将空字符串放入主键中，因为您不能将 aNULL放入主键中。
My advice? Use a technical primary key of say a number (auto-incremented in MySQL/SQL Server, from a SEQUENCEin Oracle). Put a unique index on the pair of the other fields. So:
我的建议？使用技术主键说一个数字（在 MySQL/SQL Server 中自动递增，从SEQUENCEOracle 中的 a）。在这对其他字段上放置唯一索引。所以：
CREATE TABLE t1 (
id NUMBER(10) PRIMARY KEY,
sno NUMBER(10),
desc VARCHAR2(10)
);
CREATE SEQUENCE t1_seq;
回答by TomTom
desc should not never ever be in the primary key. It simply has no need for it. Read up on waht a primary key is, in relational database theory.
desc 永远不应该在主键中。它根本不需要它。仔细阅读关系数据库理论中的主键。
That said, what you want is not possible - per relational theory, primary keys are not allowed t ocontain "undefined" values, as they have to be unique - this is part of the trinary logic SQL deploys.
也就是说，你想要的是不可能的——根据关系理论，主键不允许包含“未定义”的值，因为它们必须是唯一的——这是 SQL 部署的三元逻辑的一部分。
Someone REALLY made a bad job on your database design. Firing bad.
有人真的在你的数据库设计上做得不好。射击不好。
回答by Csasal
Use a CHAR instead of VARCHAR. Empty will be blank. It'd be needed to set a whitespace instead of empty string to avoid Oracle to convert the empty string into NULL. Note that on a CHAR field a whitespace is the same as two whitespaces, three whitespaces... (but it's different to zero whitespaces for Oracle).
使用 CHAR 而不是 VARCHAR。空将是空白。需要设置一个空格而不是空字符串，以避免 Oracle 将空字符串转换为 NULL。请注意，在 CHAR 字段上，空格与两个空格、三个空格...相同（但对于 Oracle 与零空格不同）。
