Re: Inserting a new PK into an existing table

Mark D Powell wrote:

Normally we use a business column or set of column values in the table
to be the PK and do not use an artificial key since if a unique
business value exists there is no need for or real use of an artificial
key.

In general, I would agree. In this specific case however, I think it
could be desirable to have an additional primary key column because:

a) The "old" primary key is composed of multiple columns (whose
specific combinations remain unique, which will still be enforced by a
unique constraint).

b) Unlike before, the table is now going to be referenced by several
other tables via foreign keys. Without the new artificial primary key,
this would require multiple new columns in all of these tables (along
with a foreign key that is composed of these columns).

I thought the new primary key would be a good idea to reduce redundancy
and simplify the structure, because now I only need to add one new
column to each of the other tables. Or is it better to use a different
approach?

You can populate a numeric column with unique values by performing an
update statement that references the rownum for each row in the table.
[...]

Perfect solution. Thank you!

Jens

.

Relevant Pages

  • Re: How do I discover a tables primary key?
    ... sp_helpconstraints system procedure in isql the result looks like ... an index once I have an entry in sysindexes. ... -- Number of references made by this table: ... from within my program I only get back one row, the PRIMARY KEY ...
    (comp.databases.sybase)
  • Re: How do I discover a tables primary key?
    ... calling sp_helpconstraint from isql gives different ... -- Number of references made by this table: ... But when I execute the same statement ("sp_helpconstraint City") ... from within my program I only get back one row, the PRIMARY KEY ...
    (comp.databases.sybase)
  • Re: Access as a RDBMS--why the multiple relationships?
    ... non-unique field in TABLE B. In the relationship diagram this shows ... referenced field is not a primary key, ... (CompositePartNo Integer references PARTS.PartNo, ... ComponentPartnentNo Integer references PARTS.PartNo, ...
    (comp.databases.theory)
  • Re: How do I discover a tables primary key?
    ... -- Number of references made by this table: ... But when I execute the same statement ("sp_helpconstraint City") ... from within my program I only get back one row, the PRIMARY KEY ... Why does the system procedure return ...
    (comp.databases.sybase)
  • Re: object model to table design mapping problem
    ... the INCITS H2 Database Standards Committee(nee ANSI X3H2 ... NOT NULL PRIMARY KEY, ... REFERENCES Courses, ... ON UPDATE CASCADE ...
    (microsoft.public.sqlserver.programming)