conditional primary key based on value not being null-CodePudding

I have a table that has a composite primary key:

   CREATE TABLE tb (
        it_service               VARCHAR2(200),
        hostname                 VARCHAR2(255),
        sw_name                  VARCHAR2(200 CHAR),
        product_home             VARCHAR2(500),
        product_home_ods         VARCHAR2(500),
        instance_name            VARCHAR2(255),
        eg_status                VARCHAR2(10),
       );

ALTER TABLE tb ADD (
    CONSTRAINT pk_tb PRIMARY KEY (              hostname,
                                                instance_name,
                                                product_home,
                                                product_home_ods,  
                                                it_service,
                                                sw_name )
);

The problem is that when I insert into this table, the data from the product_home column is null for the rows where product_home_ods has data. The same applies product_home_ods, the rows where that table contain data are null for product_home.

Example:

product_home product_home_ods
java         null
python       null
null         windows
null         windows_server

Is it possible to create a primary key based on the condition of these columns ? I understand a primary key should not contain null values, but unsure about the best way to tackle this problem.

CodePudding user response：

And changing to this model ? product_home will never be NULL and ods_flag will indicate if it should be interpreted a product_home or product_home_ods, something you can do in a view to get back the same data as your original table.

CREATE TABLE tb (
        it_service               VARCHAR2(200),
        hostname                 VARCHAR2(255),
        sw_name                  VARCHAR2(200 CHAR),
        product_home             VARCHAR2(500),
        ods_flag                 NUMBER(1,0),
        instance_name            VARCHAR2(255),
        eg_status                VARCHAR2(10),
       );

ALTER TABLE tb ADD (
    CONSTRAINT pk_tb PRIMARY KEY (              hostname,
                                                instance_name,
                                                product_home,
                                                ods_flag,  
                                                it_service,
                                                sw_name )
);

(As long as product_home and product_home_ods of your original table are not foreign keys)