July 16, 2008 at 3:40 am
Hello all,
I have a table, MAIL_INTERESTS, that has these 2 fields:
[MAILNO] [int],
[InterestID] [varchar](50)
Now, I'm having a problem on how to create primary key/s for this table. I read up on internet on mix responses on adding an identity field as a key. some say it's bad and some say it's ok.
i'm confused on the best approach to create the primary keys because i'm worried if it will affect the performance of the table in the future.
Can any pro here give any suggestions on what I should do.
TIA.
July 16, 2008 at 5:32 am
Is Your MailNO a unique column or does it allow duplicates. If your MailNo is a unique column and you want to build a primary key on the column it is good idea to create identity on the column and make it a primary key with Clustered Index.
Please post your exact requirments.
Prasad Bhogadi
www.inforaise.com
July 16, 2008 at 7:39 pm
My MAILNO is not a unique column, it is a FK to another table called 'MAIL'. Each MAILNO have 1/more interests and it is stored in this table. MAILNO is already an identity in MAIL table.
InterestsID is also a FK to another table called 'Interests Master'.
I was wondering if i could create composite key but would it affect insert/delete/update for this table? How to create a composite key.
I have tried inserting a new column in this MAIL_INTERESTS table called MailInterestsID (int, identity). But when i do search for MAIL and MAIL_INTERESTS, it takes longer than usual. I compared this by creating the same database in Access.
If i understand correctly, the table wont be updatable if there is no PK.
July 16, 2008 at 10:47 pm
looking at the structure can u tell, how much is the Selectivity of the columns? Search work on which columns?
Does MailNo and InterestID both are unique in table?
July 17, 2008 at 12:05 am
I think it'll be clearer if I list my tables here.
MAIL:
[MAILNO] int, identity, primary key
[DATEJOIN] smalldatetime
[RETAILER] bit
[TITLE] varchar
[NAME_ML] varchar
[ADDRESS] varchar
Interests Master:
[InterestID] varchar, primary key
[Description] varchar
MAIL_INTERESTS:
[MAILNO] int, foreign key to MAIL table
[InterestID] varchar, foreign key to 'Interests master' table
If the two fields are combined, MAILNO and InterestID then it is unique.
I'd like to create a PK for MAIL_INTERESTS table but I'm in vague of which is the best primary key.
July 21, 2008 at 12:20 am
I have so many tables in my database that have such a structure. You always fall in this situation when you are mapping a PK of one table and with teh PK of another table to create on-to-many relationship with each otther. These tuyep of entities are called Associate Entities (if I am not wrong.)
Ok, Sol 1
I always add an Identity column and take it as a PK of the Mapped table (MAIL_INTERESTS in your case.)
Sol 2
The another way is to make a composite PK on both columns. This is also right but ONLY IF your DO NOT want your MAIL_INTERESTS PK to be used as referance as FK in any other table(s). In this situation, you will need to add two columns in the referanced table(s). In the prior solution, only one column will be added in the referanced table(s).
Choice is yours.
I always go for Sol 1.
Atif Sheikh
July 21, 2008 at 2:52 pm
MAIL_INTERESTS is just an intersection table to implement a many-to-many relationship between MAIL and INTERESTS tables. The combination of the two fields is a key. It generally makes no sense to create an additional Identity field to serve as PK for an intersection table -- it can never be used in queries and FK references to intersection tables are not common. If you make the fields the composite PK, then the system will not allow nulls and enforces that each combination must be unique. If you create a surrogate key, you will then have to make them NOT NULL and create a unique index on your own.
So go ahead and make them the PK. Contrary to what you may hear elsewhere, composite keys are not evil, they won't hurt you. 😀
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
July 21, 2008 at 8:30 pm
Thank you all for your replies.
I will have no problem if i add a new identity column and make it a PK. and this is the quickest solution. 🙂
If I were to make both column primary keys, would it effect anything in the future that I should be aware of? e.g: performance in searching records,maintenance.
July 21, 2008 at 8:48 pm
Eera (7/21/2008)
Thank you all for your replies.I will have no problem if i add a new identity column and make it a PK. and this is the quickest solution. 🙂
If I were to make both column primary keys, would it effect anything in the future that I should be aware of? e.g: performance in searching records,maintenance.
In this case, there really is no reason to add an identity column and make it the PK. All that is going to do is add another column which won't be used anywhere and force you to create a unique index covering the other two columns.
The quickest solution is to create your primary key on the combination of the two columns.
Since you have to create an index covering both columns anyways - the only performance hit you are going to see is if you add another column and index that column also. Granted, the performance hit will be neglible - but it is still there.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply