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?
Why don't you introduce a real primary key attribute (like id) which is auto incremented and create an index over sno and desc?
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;
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.
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).
