asked on

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!

ASKER CERTIFIED SOLUTION

unknown_routine

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rey Obrero (Capricorn1)

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)
);

Megin

ASKER

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!

unknown_routine

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.

:)

Megin

ASKER

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

Thank you!