How to get a primary key with null values ​​using an empty string?

Question

I have a table in which I need both values ​​to be primary, because I refer to this combination as a foreign key in other tables. The definition of the table and the data I need to supply 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> 

The problem here desc can sometimes be zero. The primary key cannot be null, so when I encounter a null value, I just insert into the table. The problem here is in some cases desc may have an empty string. If I insert the data 100, Null and 100, “these are two different things, but I can’t put them in the table. I don’t want to put some row like“ EMPTY ”if null, because it can confuse the end user who is watching to the table.

1) How can I handle the null register for desc, having it as a primary key. I can not use automatic sequence number. 2) How can I distinguish between an empty string entered by me and one that already exists?

Answer 1

Why don't you introduce a real primary key attribute (e.g. id) that automatically increments and creates an index by sno and desc?

Answer 2

Oracle treats empty VARCHAR as NULL synonyms, which is somewhat inconsistent, but you need to live with that. Thus, you cannot put an empty string in the primary key, because you cannot put NULL in the primary key.

My advice? Use a technical primary key, such as a number (automatically added to MySQL / SQL Server, from SEQUENCE to Oracle). Put a unique index in a couple of other fields. So:

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

Answer 3

desc should never be in the primary key. He just doesn't need it. Read the primary key on waht, in relational database theory.

However, what you want is impossible - according to the theory of relations, primary keys are not allowed to use undefined values, since they must be unique - this is part of the deployment of SQL logic. p>

Someone REALLY did a poor job in your database project. Dismissal is bad.

Answer 4

Use CHAR instead of VARCHAR. Empty will be empty. It would be necessary to set a space instead of an empty string so that Oracle does not convert the empty string to NULL. Note that in the CHAR field, the space matches two spaces, three spaces ... (but it differs from zero by spaces for Oracle).