Solved

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

Posted on 2013-11-07
5
488 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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
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…

809 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