?
Solved

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

Posted on 2013-11-07
5
Medium Priority
?
683 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 2000 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying 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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

807 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