By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,454 Members | 2,633 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,454 IT Pros & Developers. It's quick & easy.

Dual Primary Key

P: n/a
I have come accross a situation where I would like a primary key that is
based upon 2 fields. Firstly is this possible, and secondly is it good
practice?

Thanks
Neil
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
"Neil" <ne**@logan-5.co.uk> wrote in message
news:cl**********@news6.svr.pol.co.uk...
I have come accross a situation where I would like a primary key that is
based upon 2 fields. Firstly is this possible, and secondly is it good
practice?

yes and yes.

create table neil
(
col1 varchar(50) not null,
col2 varchar(50) not null,
constraint PK_neil_col1_col2
primary key
)
If you want to do this from the UI you can open the table in design view and
use SHIFT key to select multiple columns.
Nov 13 '05 #2

P: n/a
Thanks a lot for that John

"John Winterbottom" <as******@hotmail.com> wrote in message
news:2t*************@uni-berlin.de...
"Neil" <ne**@logan-5.co.uk> wrote in message
news:cl**********@news6.svr.pol.co.uk...
I have come accross a situation where I would like a primary key that is
based upon 2 fields. Firstly is this possible, and secondly is it good
practice?

yes and yes.

create table neil
(
col1 varchar(50) not null,
col2 varchar(50) not null,
constraint PK_neil_col1_col2
primary key
)
If you want to do this from the UI you can open the table in design view
and use SHIFT key to select multiple columns.

Nov 13 '05 #3

P: n/a
"Neil" <ne**@logan-5.co.uk> wrote in message news:<cl**********@news6.svr.pol.co.uk>...
I have come accross a situation where I would like a primary key that is
based upon 2 fields. Firstly is this possible, and secondly is it good
practice?

Thanks
Neil


Possible, yes. Ctrl-click on the two fields and then hit the Primary
Key button.
Practical? Maybe. If records in this table will have child records, I
would add an autonumber field, and set it's values to Unique. Then
use that as a foreign key. otherwise the FK business gets really
ugly.
Nov 13 '05 #4

P: n/a
"Neil" <ne**@logan-5.co.uk> wrote in message
news:cl**********@news6.svr.pol.co.uk...
I have come accross a situation where I would like a primary key that is
based upon 2 fields. Firstly is this possible, and secondly is it good
practice?

Thanks
Neil

In the design view of the table, you can simply highlight 2 fields
simultaneously and then mark that combination as being the primary key.
Don't forget that you can create an index on a table which is made up of 2
fields and is unique but not the primary key. For example, you could,
although I wouldn't suggest as a real-life design, have the following table:

tblContacts:
ConID = Autonumber PK
ConFirstName
ConLastName

Where a unique index was set up to make sure you could not enter a person
with the same first and last name. This does not affect having a totally
unrelated primary key which might be useful if I had the following table:

tblContactNotes:
NteID PK Autonumber
NteConID Long - The contact ID for this note
NteDate The date
NteText The text for the note

If I had made the PK for tblContacts a combined field of first and last
name, I would have made it difficult to have the related key in the table of
notes. So, in short, I rarely make a table with a PK comprising more than
one field, although I often create unique multi-field indexes in addition to
the primary key.


Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.