Solved

How do I create a composite key in SQL in Access?

Posted on 2013-11-07
5
501 Views
Last Modified: 2013-11-08
I want to have a table with multiple primary keys (composite key?)

This is what I have:

CREATE TABLE SubTaskOrders
(
[stoID] Counter PRIMARY KEY NOT NULL,
[TaskOrderID] Int PRIMARY KEY NOT NULL,
[stoNo] Char(2) NOT NULL,
[StoName] Varchar(100) NOT NULL,
FOREIGN KEY (TaskOrderID) REFERENCES TaskOrders (TaskOrderID)
);

Open in new window



I have also tried this:
CREATE TABLE SubTaskOrders
(
[stoID] Counter  NOT NULL,
[TaskOrderID] Int  NOT NULL,
[stoNo] Char(2) NOT NULL,
[StoName] Varchar(100) NOT NULL,
PRIMARY KEY (stoID, TaskOrderID)
FOREIGN KEY (TaskOrderID) REFERENCES TaskOrders (TaskOrderID)
);

Open in new window


Neither are working. I have another table that gets more complicated, with multiple foreign keys and primary keys.

I have lots of examples, but I am working in Access, which seems to make it more difficult.

Help!
0
Comment
Question by:Megin
  • 2
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
unknown_routine earned 500 total points
ID: 39631580
try this:

CREATE TABLE SubTaskOrders
(
[stoID] Counter  NOT NULL,
[TaskOrderID] Int  NOT NULL,
[stoNo] Char(2) NOT NULL,
[StoName] Varchar(100) NOT NULL,
PRIMARY KEY (stoID, TaskOrderID),
FOREIGN KEY (TaskOrderID) REFERENCES TaskOrders (TaskOrderID)
);
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39631728
try this


CREATE TABLE SubTaskOrders
(
[stoID] Counter  NOT NULL,
[TaskOrderID] Int  NOT NULL,
[stoNo] Char(2) NOT NULL,
[StoName] Varchar(100) NOT NULL,
CONSTRAINT pk_stoID PRIMARY KEY (stoID, TaskOrderID)
FOREIGN KEY (TaskOrderID) REFERENCES TaskOrders (TaskOrderID)
);
0
 

Author Closing Comment

by:Megin
ID: 39631947
That worked, but I can't see where it was different than my second example. What did you do? I know it had to be different, but how?

And, thank you!
0
 
LVL 15

Expert Comment

by:unknown_routine
ID: 39633707
Only a small difference, you forgot to put a comma","

Between

PRIMARY KEY (stoID, TaskOrderID),

and

FOREIGN KEY (TaskOrderID) REFERENCES TaskOrders (TaskOrderID)



This kind of mistakes happens to all of us when we try to do something really fast.

:)
0
 

Author Comment

by:Megin
ID: 39633753
Those little things kill me!  No matter how hard I try to find them, I alway end up missing something.

Thank you!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question