How to have a primary key with null values using empty string ?

Question

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.

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?

Answer 1

Oracle treats empty VARCHAR fields 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 NULL into a primary key.

My advice? Use a technical primary key of say a number (auto-incremented in MySQL/SQL Server, from a SEQUENCE in Oracle). Put a unique index on the pair of the other fields. So:

CREATE TABLE t1 (
  id NUMBER(10 PRIMARY KEY,
  sno NUMBER(10),
  desc VARCHAR2(10)
);
CREATE SEQUENCE t1_seq;

Answer 2

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

Answer 3

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.

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.

Someone REALLY made a bad job on your database design. Firing bad.

Answer 4

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).