Creating composite primary keys in SQL

P

PJW

I would like to create a number of composite primary keys in Access. Is it
possible to create them using a query rather than the Index menu for each
table? For instance, I can define a single variable as a primary key:

CREATE TABLE MyNewTable
(PersonID LONG CONSTRAINT PrimaryKeyName PRIMARY KEY,
PersonName TEXT(50),
Age BYTE);

Is there a way of combining more fields into the PrimaryKeyName index? Also,
can the foreign key for another table be defined at this stage or is an
update required?

Many thanks for any suggestions.
 
R

Roger Carlson

If you only want a single field to be the primary key, you can put the
constraint in the field list. However, if you want a composite PK, you put
the Constraint after the field list:

CREATE TABLE MyNewTable (PersonID LONG, PersonName TEXT(50), CONSTRAINT
PrimaryKeyName PRIMARY KEY (PersonID, PersonName));

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
P

PJW

Thanks Roger - that now works.

Can you suggest any syntax for defining the Foreign Key from another table
whilst creating the original table?

PJW
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top