How can i make uniqueness in a sql table with using columns?-CodePudding

in my table there is no unique column , but i need to make uniqueness with using columns , adding a new column is worst option , i need to do this something like concat two or more columns how can i do that ?

i need to make uniqeness in my sql table with using two or more columns , adding a new columns is worst option , i need a solution which is concat two or more columns something like that how can i do that ?

CodePudding user response：

One option is to create a unique index on set of columns you want to enforce uniqueness on.

For example:

create unique index ui1_test on test (id, name, address);

Doing so, users won't be able to insert new rows (or update existing ones) to a combination (of these columns) that already exists in the table as Oracle will raise an error.

CodePudding user response：

Oracle
There is a way to check for "uniqueness" if you compare distinct values of different field combinations with total number of rows. In below example the score 0 (zero) means uniqueness:

WITH
  tbl AS
      (
          Select 1 "COL_1", 'AXC' "COL_2", 'khgh jklj' "COL_3" From Dual Union All
          Select 3 "COL_1", 'AXC' "COL_2", 'khgh jklj' "COL_3" From Dual Union All
          Select 3 "COL_1", 'DEF' "COL_2", 'khgh jklj' "COL_3" From Dual Union All
          Select 1 "COL_1", 'DEF' "COL_2", 'xxxx yyyy' "COL_3" From Dual  
      )
Select 
    Count(*) "TOTAL",
    Count(*) - Count(DISTINCT COL_1) "COL_1_SCORE",
    Count(*) - Count(DISTINCT COL_2) "COL_2_SCORE",
    Count(*) - Count(DISTINCT COL_3) "COL_3_SCORE",
    Count(*) - Count(DISTINCT COL_1 || COL_2) "COL_1-2_SCORE",
    Count(*) - Count(DISTINCT COL_1 || COL_3) "COL_1-3_SCORE",
    Count(*) - Count(DISTINCT COL_2 || COL_3) "COL_2-3_SCORE",
    Count(*) - Count(DISTINCT COL_1 || COL_2 || COL_3) "COL_1-2-3_SCORE"
From
    tbl

/*  R e s u l t:
     TOTAL COL_1_SCORE COL_2_SCORE COL_3_SCORE COL_1-2_SCORE COL_1-3_SCORE COL_2-3_SCORE COL_1-2-3_SCORE
---------- ----------- ----------- ----------- ------------- ------------- ------------- ---------------
         4           2           2           2             0             1             1               0
*/

CodePudding user response：

You can combine a handful of columns into a unique row id with the following SQL code. I have done this when making data sets for machine learning in BigQuery.

select distinct
cast(to_hex(SHA1(concat(column1, column2, column3, column4))) as string) as observation_id
from table

Of course you need to check beforehand, that the combination of these columns produces unique rows.