Using two columns as primary key. Can I keep one column empty?-CodePudding

I have a requirement where I need to uniquely identify a row based on two columns.

 nType | dType | severity
------ ------- ----------
  down | 6500 | CRITICAL

Combination of nType and dType should always be unique. So, I created a table using

CREATE TABLE IF NOT EXISTS severitymapping(nType text, dType text, severity text, 
PRIMARY KEY (nType, dType);

But there can be a case when dType is empty. So, that too is unique for my business logic. But database will not allow empty/null value of dType in the database.

Is there a way to achieve this?

CodePudding user response：

For MySQL only (the question is MySQL-tagged).

Primary key expression cannot include NULLable columns.

If you need some column value to be NULL (not "empty" - there is no such term in SQL) then you cannot create PRIMARY key which includes this column. But you may create UNIQUE index - it allows NULLs. In this case you need in another expression to be primary key - autoincremented synthetic PK is reasonable.

Also the datatype for each column must be reasonable - the values shown and TEXT datatype does not match this. VARCHAR for nType and INT for dType looks like more suitable.

And TEXT column cannot be used in the index without the indexed prefix length specified.

So use something close to

CREATE TABLE IF NOT EXISTS severitymapping(
    nType VARCHAR(255), 
    dType INT, 
    severity TEXT, 
    UNIQUE (nType, dType),
    id INT AUTO_INCREMENT PRIMARY KEY
);

CodePudding user response：

It isn't possible to have a null or empty value for any of the columns in a primary key in Cassandra.

The partition key (nType) uniquely identifies the partition and its hash value determines the node on which it is stored.

The clustering key (dType) uniquely identifies a row within a partition. Without it, it is not possible to store the row in the partition because the row cannot be retrieved without the clustering key. Cheers!

CodePudding user response：

In your case, you are keeping nType as the partition key and dType as the clustering key.

Cassandra does not allow null clustering key values.

If you really need "no value" for some reason, then use an empty string OR some other special literal value like 'UNDEFINED' to cluster those together.

This question can help you too.