如何使用空字符串获取具有空值的主键? [英] How to have a primary key with null values using empty string?

问题描述

我有一个表，其中两个值都必须是主值，因为在其他表中我将此组合称为外键.表定义和我需要放入的数据如下

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>

这里的问题是desc有时可以为null.主键不能为null，因此当我遇到null值时，我只是在表中插入".这里的问题是有时desc可能有空字符串. 如果我插入about数据100，Null和100，则"是两个不同的东西，但是我无法将它们放在表中.我不希望将诸如'EMPTY'之类的字符串设置为null，因为它可能会使正在查看表的最终用户感到困惑.

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.

1)在将desc作为主键的同时，如何处理null大小写.我不能使用自动序列号. 2)如何区分我引入的空字符串和已经存在的空字符串?

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?

推荐答案

为什么不引入真正的主键属性(如id)，该属性会自动递增并在sno和desc上创建索引?

Why don't you introduce a real primary key attribute (like id) which is auto incremented and create an index over sno and desc?

这篇关于如何使用空字符串获取具有空值的主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！