-1

How to identify a record in a table without a primary key in Oracle 11 or 12 to identification was correct even after the export , import and manipulate tables ( rowid for this reason will not be a good solution )

Tomasz
  • 150
  • 1
  • 14
  • 1
    You need to explain your situation a little more. Presumably by "record" you mean "row" (there are no "records" in Oracle tables). What do you mean by "identify"? Clearly rowid does identify a row, so perhaps you meant something else by "identify". If there is no PK, perhaps your table even has duplicates? In that case what does "identify" mean, if not rowid? –  Aug 11 '16 at 14:57
  • Welcome to SO; here you find something on [ask] and how to build a [mcve] – Aleksej Aug 11 '16 at 14:59
  • 1. Yes ROW no Record:) 2. Identyfy - for update. 3. Table without PK and UK.... 4. rowid may change - after backup, import, table shrink or other operations... – Tomasz Aug 11 '16 at 15:01
  • You can only identify a single row if there is something unique about it; you don't have a PK or UK, but may have a unique index, or a column or combination of columns you know is unique anyway - but if you're going to rely on that there should be a constraint to make sure it stays unique. You already know you can't rely on the 'physical' locator rowid, so there has to be a logical way; as only you know the table, data and any indexes/constraints it has, we can't really be specific. – Alex Poole Aug 11 '16 at 15:09
  • Unfortunately I have some tables with no primary key or unique - and I can not wear them because the database is not my ... – Tomasz Aug 11 '16 at 15:12
  • I hope that the oracle has yet another unique internal record number (internal PK) - which never changes ... – Tomasz Aug 11 '16 at 15:19
  • Identify for update? (and similarly for joins, etc.) - You don't need a PK for that. You can "identify" a row by the value in any column or combination of columns, even for duplicates. For example, you have a column "name" with names in it and a column "age" which right now you has NULL in every row. You can "update" to put the age 35 for every row where "name" = 'Tomasz'. The identification is with a WHERE filter. –  Aug 11 '16 at 15:25
  • Nope, afraid not. You mentioned MVs in another comment; [from the docs](http://docs.oracle.com/cd/E11882_01/server.112/e10706/repmview.htm#REPLN107), "If you reorganize or truncate your master table, then your rowid materialized view must be COMPLETE refreshed because the rowids of the master table have changed". – Alex Poole Aug 11 '16 at 15:26
  • @AlexPoole ok thanks for info with complete refresh. – Tomasz Aug 11 '16 at 15:54
  • @mathguy this is no my database ... – Tomasz Aug 11 '16 at 15:55
  • If your table has no unique constraints at all, then by definition the only logically useful definition of a unique identifier would be a concatenation of all the columns. Note that if two rows have all the same data, by definition they are referring to the same ("real world") thing, so one of them can be safely discarded or ignored. – Jeffrey Kemp Aug 12 '16 at 03:59

1 Answers1

-1

Referring to these Oracle Docs

every row within a table have RowID pseudocolumn, RowID is always unique containing the address of that row in Dictionery,But Standards says,you should use your own created PK.If your table have no PK, then create a PK or Make some suitable column Unique

Zaid Mirza
  • 3,540
  • 2
  • 24
  • 40
  • 1
    After importing the database rowid change - because they are the physical addresses of the records , and these are changing ... – Tomasz Aug 11 '16 at 15:00
  • @TomaszTomzik as I stated,If your table have no PK, then create a PK or Make some suitable column Unique – Zaid Mirza Aug 11 '16 at 15:05
  • @TomaszTomzik then you have to go with the wind or ask DBA for this – Zaid Mirza Aug 11 '16 at 15:10
  • So you do not know how to do it . I wonder how does Oracle EE materialized views on tables where there is no primary keys .... – Tomasz Aug 11 '16 at 15:15
  • I hope that the oracle has yet another unique internal record number (internal PK) - which never changes ... – Tomasz Aug 11 '16 at 15:20
  • @TomaszTomzik everything is in DataDictionery of Oracle DB, you should not fight with DB. – Zaid Mirza Aug 11 '16 at 15:23