Create a unique primary key (hash) from database columns

Question

I have this table that does not have a primary key.

I am going to insert some records into a new table to analyze them, and I am thinking of creating a new primary key with values ​​from all available columns.

If it were a programming language such as Java, I would:

int hash = column1 * 31 + column2 * 31 + column3*31 

Or something like that. But this is SQL.

How to create a primary key from the values ​​of available columns? I cannot just mark all the columns as PK, since I need to compare them with data from another DB table.

My table has 3 numbers and a date.

EDIT What's my problem

I think a little more background is needed. I apologize for not providing it before.

I have a database (dm) that is updated every day from another db (source). He has records for the last two years.

Last month (July), the update process was disrupted, and for a month the data was not updated in dm.

I manually create a table with the same structure in my Oracle XE, and I copy the records from the original source to my db (myxe). I copied only the entries from July to create the report needed by the end of the month.

Finally, in August 8, the update process was fixed, and the records that were waiting for the transfer of this automatic process were copied to the database (from source to dm).

This process cleans the original data source after copying it (in dm).

Everything looks great, but we just realize that the number of records is lost (about 25% of July)

So what I want to do is use my backup (myxe) and paste all these records into the database (dm).

The problem is here:

• They do not have a clearly defined PC.
• They are in different databases.

So, I thought that if I could create a unique pk from both tables that gave the same number, I could say that they were missing and insert them.

EDIT 2

So, I did the following in my local environment:

 select a.* from the_table@PRODUCTION a , the_table b where a.idle = b.idle and a.activity = b.activity and a.finishdate = b.finishdate 

Which returns all rows that are present in both databases (join ...?) I have 2000 records.

What will I do next, delete them all from the target db, and then just paste them all from my db into the target table.

I hope I don’t get into something worse: - S: -S

Answer 1

Just create a surrogate key:

 ALTER TABLE mytable ADD pk_col INT UPDATE mytable SET pk_col = rownum ALTER TABLE mytable MODIFY pk_col INT NOT NULL ALTER TABLE mytable ADD CONSTRAINT pk_mytable_pk_col PRIMARY KEY (pk_col) 

or that:

 ALTER TABLE mytable ADD pk_col RAW(16) UPDATE mytable SET pk_col = SYS_GUID() ALTER TABLE mytable MODIFY pk_col RAW(16) NOT NULL ALTER TABLE mytable ADD CONSTRAINT pk_mytable_pk_col PRIMARY KEY (pk_col) 

The latter uses GUID that are unique in databases, but consume more spaces and generate much slower (your INSERT will be slow)

Update:

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

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

Note that the tables must be the same up to one row (that is, have the same number of rows with the same data in them).

Update 2 :

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

If you just want to select entries that are not in dm , use this (on the dm side)

 SELECT * FROM mytable@myxe MINUS SELECT * FROM mytable 

This will return all entries that exist in mytable@myxe , but not in mytabl e@dm

Note that it will compress all duplicates, if any.

Answer 2

The danger of creating a hash value by combining three numbers and a date is that it may not be unique and therefore cannot be used safely as a primary key.

Instead, I would recommend using an auto-increment identifier for your primary key.

Answer 3

Assuming you're unique ... you can do almost the same thing in SQL. The only problem would be converting the date to a numeric value so that you can hash it.

 Select Table2.SomeFields FROM Table1 LEFT OUTER JOIN 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 will work for SQL Server, the only difference for Oracle will be how you handle the date conversion. In addition, there are other functions for converting dates in SQL Server, so this is far from the only solution.

And you can combine this with the Quassnoi SET statement to populate a new field. Just use the left side of the join condition logic for the value.

Answer 4

If you load a new table with values ​​from the old table, and then you need to join the two tables, you can only "correctly" 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 change the old table first by adding a new column.

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

If the uniqueness of the hash code cannot be guaranteed, you may need to set up a primary key consisting of as many columns as possible to ensure uniqueness (for example, a natural key). If there is no natural key, well, I heard that once Oracle provides a rownum for each row of data, could you use this?