Creating composite primary keys in SQL

[Guest]

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.

 

[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

 

[Guest]

Thanks Roger - that now works.

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

PJW