"Rathtap" <amcniw@yahoo.com> wrote in message
news:b21a5958.0308211116.2ff8ada6@posting.google.com...
> I want to use the Identity field (increment 1,1) as a primary key and
> have a unique constraint on my other field which is of type char.
> I am worried that related data in other tables may lose referntial
> integrity if records in the ID table get messed up and need to be
> re-entered.
> Can you please advice on best way to do this. I definitely need a
> numeric id field because it makes the joins and queries so much
> faster.

> I want to use the Identity field (increment 1,1) as a primary key and
> have a unique constraint on my other field which is of type char.
> I am worried that related data in other tables may lose referntial
> integrity if records in the ID table get messed up and need to be
> re-entered.
> Can you please advice on best way to do this. I definitely need a
> numeric id field because it makes the joins and queries so much
> faster.

Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns93DEEC8D122DYazorman@127.0.0.1>...
> Rathtap (amcniw@yahoo.com) writes:
> > I want to use the Identity field (increment 1,1) as a primary key and
> > have a unique constraint on my other field which is of type char.
> > I am worried that related data in other tables may lose referntial
> > integrity if records in the ID table get messed up and need to be
> > re-entered.
> > Can you please advice on best way to do this. I definitely need a
> > numeric id field because it makes the joins and queries so much
> > faster.
>
> Have you actually benchmarked this?
>
> If you have a natural key, use it, and don't make your system more
> complex than necessary.

> The issue here is that these tables are being used to populate cubes.
> We have found that there is a tremendous performance gain when the
> Primary and Foreign key tables are joined using int fields as opposed
> to using chars.

> I therefore cannot make my foreign key reference a unique constraint.
> Let me explain with an example.
> Table Procedures has fields ProcID(int, PK, Identity(1,1)),ProcCode
> (char) and ProcDescription (char).
> Table Patient has amongst others ProcID which is a foreign key.
> My dilemma is, to keep the database and the programming simple - I
> want ProcID to be an identity column, however, I am worried that
> should something happen to the Procedures table then the relationship
> between the two tables will be lost.
> What are the best practices in this situation? I simply cannot have a
> char field for FK/PK.
> One solution is to store both the ProcedureID and ProcedureCode fields
> in the Patient table so that even if the Procedures table has to be
> recreated, I can run a procedure to update the FKs in the Patient
> table.

Erland Sommarskog <sommar@algonet.se> wrote in message news:<Xns93E0DCBD555A5Yazorman@127.0.0.1>...
> Rathtap (amcniw@yahoo.com) writes:
> > The issue here is that these tables are being used to populate cubes.
> > We have found that there is a tremendous performance gain when the
> > Primary and Foreign key tables are joined using int fields as opposed
> > to using chars.
>
> How long are the char columns?

>
> True, that character comparison is more complex, because of collation
> rules (unless you use a binary collation). But the overhead is not likely
> to be more than say 20%. And that is not a tremendous difference in the
> database world. :-)
>
> Maybe there were other problems, for instance joining a char and nchar
> column leads to auto-conversion that precludes use of indexes.
>
> > I therefore cannot make my foreign key reference a unique constraint.
> > Let me explain with an example.
> > Table Procedures has fields ProcID(int, PK, Identity(1,1)),ProcCode
> > (char) and ProcDescription (char).
> > Table Patient has amongst others ProcID which is a foreign key.
> > My dilemma is, to keep the database and the programming simple - I
> > want ProcID to be an identity column, however, I am worried that
> > should something happen to the Procedures table then the relationship
> > between the two tables will be lost.
> > What are the best practices in this situation? I simply cannot have a
> > char field for FK/PK.
> > One solution is to store both the ProcedureID and ProcedureCode fields
> > in the Patient table so that even if the Procedures table has to be
> > recreated, I can run a procedure to update the FKs in the Patient
> > table.
>
> I'm not sure that I follow your example, but if you insist of having the
> extra column, this is how you could do it:
>
> CREATE TABLE parent
> (parentid int IDENTITY PRIMARY KEY, -- artificial key
> parentcode char(n) NOT NULL UNIQUE, -- natural key
> other_columns...,
> UNIQUE (parentid, parentcode))
>
> CREATE TABLE child
> (childid some_type NOT NULL PRIMARY KEY,
> ...
> parentid int NOT NULL REFERENCES parent(parentid),
> parentcode char(n) NOT NULL REFERENCES parent(parentcode),
> ...
> FOREIGN KEY (parentid, parentcode)
> REFERENCES parent (parentid, parentcode)
>
> This ensures that id:s and codes are unique in the parent table,
> you don't have orphan id:s and codes in the child table, and that
> id:s and codes are in sync in both tables. The price you pay is
> an extra redundant UNIQUE constraint on the parent table.
>
> Note: for brevity I have not named the constraints in this outline.
> However, in actual code I recommend that you always name your
> constraints (save for temp tables and table variables).
>
> Then again, if you don't have parentcode at all in the child table,
> you can always retrieve the parentcode by joining to the parent
> table.

> The columns are not long -- 15 to 20. The problem is that there are a
> few million records and that is why it is slow.

>> I'm not sure that I follow your example, but if you insist of having the
>> extra column, this is how you could do it:
>>
>> CREATE TABLE parent
>> (parentid int IDENTITY PRIMARY KEY, -- artificial key
>> parentcode char(n) NOT NULL UNIQUE, -- natural key
>> other_columns...,
>> UNIQUE (parentid, parentcode))
>>
>> CREATE TABLE child
>> (childid some_type NOT NULL PRIMARY KEY,
>> ...
>> parentid int NOT NULL REFERENCES parent(parentid),
>> parentcode char(n) NOT NULL REFERENCES parent(parentcode),
>> ...
>> FOREIGN KEY (parentid, parentcode)
>> REFERENCES parent (parentid, parentcode)
>>
> This is what I want to do but am not sure is the best way to do it.
> Reason being if my parentcode in the parent table is lost, how do I
> match it up with the parentid in the child. If I re-insert values in
> the parent, the id's generated will be out of synch with those in the
> child.

Rathtap wrote:
> I want to use the Identity field (increment 1,1) as a primary key and
> have a unique constraint on my other field which is of type char.
> I am worried that related data in other tables may lose referntial
> integrity if records in the ID table get messed up and need to be
> re-entered.

>> BTW: The guru of DW (Ralph Kimball) recommends the use of natural