Create A Unique Primary Key (hash) From Database Columns

I have this table which doesn't have a primary key. I'm going to insert some records in a new table to analyze them and I'm thinking in creating a new primary key with the values f

Solution 1:

The danger of creating a hash value by combining the 3 numbers and the date is that it might not be unique and hence cannot be used safely as a primary key.

Instead I'd recommend using an autoincrementing ID for your primary key.

Solution 2:

Just create a surrogate key:

ALTERTABLE mytable ADD pk_col INTUPDATE  mytable
SET     pk_col = rownum

ALTERTABLE mytable MODIFY pk_col INTNOTNULLALTERTABLE mytable ADDCONSTRAINT pk_mytable_pk_col PRIMARY KEY (pk_col)

or this:

ALTERTABLE mytable ADD pk_col RAW(16)

UPDATE  mytable
SET     pk_col = SYS_GUID()

ALTERTABLE mytable MODIFY pk_col RAW(16) NOTNULLALTERTABLE mytable ADDCONSTRAINT pk_mytable_pk_col PRIMARY KEY (pk_col)

The latter uses GUID's which are unique across databases, but consume more spaces and are much slower to generate (your INSERT's will be slow)

Update:

If you need to create same PRIMARY KEYs on two tables with identical data, use this:

MERGE
INTO    mytable v
USING   (
        SELECT  rowid AS rid, rownum AS rn
        FROM    mytable
        ORDERBY
                co1l, col2, col3
        )
ON      (v.rowid = rid)
WHEN MATCHED THEN
UPDATE
SET     pk_col = rn

Note that tables should be identical up to a single row (i. e. have same number of rows with same data in them).

Update 2:

For your very problem, you don't need a PK at all.

If you just want to select the records missing in dm, use this one (on dm side)

SELECT*FROM    mytable@myxe
MINUS
SELECT*FROM    mytable

This will return all records that exist in mytable@myxe but not in mytable@dm

Note that it will shrink all duplicates if any.

Solution 3:

Assuming that you have ensured uniqueness...you can do almost the same thing in SQL. The only problem will be the conversion of the date to a numeric value so that you can hash it.

Select Table2.SomeFields 
    FROM Table1 LEFTOUTERJOIN Table2 ON
        (Table1.col1 *31) + (Table1.col2 *31) + (Table1.col3 *31) + 
            ((DatePart(year,Table1.date) + DatePart(month,Table1.date) + DatePart(day,Table1.date) )*31) = Table2.hashedPk

The above query would work for SQL Server, the only difference for Oracle would be in terms of how you handle the date conversion. Moreover, there are other functions for converting dates in SQL Server as well, so this is by no means the only solution.

And, you can combine this with Quassnoi's SET statement to populate the new field as well. Just use the left side of the Join condition logic for the value.

Solution 4:

If you're loading your new table with values from the old table, and you then need to join the two tables, you can only "properly" do this if you can uniquely identify each row in the original table. Quassnoi's solution will allow you to do this, IF you can first alter the old table by adding a new column.

If you cannot alter the original table, generating some form of hash code based on the columns of the old table would work -- but, again, only if the hash codes uniquely identify each row. (Oracle has checksum functions, right? If so, use them.)

If hash code uniqueness cannot be guaranteed, you may have to settle for a primary key composed of as many columns are required to ensure uniqueness (e.g. the natural key). If there is no natural key, well, I heard once that Oracle provides a rownum for each row of data, could you use that?