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

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!
MeginAsked:
Who is Participating?
 
unknown_routineConnect With a Mentor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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
 
MeginAuthor Commented:
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
 
unknown_routineCommented:
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
 
MeginAuthor Commented:
Those little things kill me!  No matter how hard I try to find them, I alway end up missing something.

Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.