How to have a primary key with null values using empty string?
Solution 1
Why don't you introduce a real primary key attribute (like id) which is auto incremented and create an index over sno and desc?
Solution 2
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;
Solution 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.
Comments
-
Ram almost 2 years
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?
-
John Saunders about 14 yearsA UNIQUE index or constraint over sno and desc.
-
Aurril about 14 yearsA normal index for faster searches and a constraint for uniqueness which treats null and empty strings since a unique index would not be possible. You can create a stored procedure which will handle the insertion of the data so uniquness is guarenteed.