reference a foreign key to a primary key within the same table

Question

I would like to reference a foreign key to a primary key within the same table, using the query design mode in ms access. This is what I tried to do:

CREATE TABLE Employees ( 
P_Id INTEGER PRIMARY KEY,
super_Id INTEGER FOREIGN KEY REFERENCES Employees(P_Id)
);

However I receive a syntax error (the word FOREGIN is highlighted). Is it at all possible to perform this task in ms access?

Edit: I switched to ANSI-92, and I also tried this format:

CREATE TABLE Employees ( 
P_Id INTEGER PRIMARY KEY,
CONSTRAINT super_Id  FOREIGN KEY (Employees)
);

but I still receive an error

Yes, what exactly is that you are trying to do? Maybe explain with simple words what you want, so we could give you a better solution. — PaulFrancis, Feb 25 '15 at 09:47
It is an employee table. Each employee has a manager, who is also an employee. — nafrtiti, Feb 25 '15 at 10:00
Why not use the relationship window? A lot of DDL will only run with ADO in VBA. — Fionnuala, Feb 25 '15 at 12:57
why should ANSI 92 cause problems? I don't want to use the relationship window since I want to practice writing SQL queries — nafrtiti, Feb 25 '15 at 15:16

score 0 · Accepted Answer · answered Mar 27 '18 at 23:03

0

For MS Access, you need to use CONSTRAINT keyword like:

CREATE TABLE Employees ( 
   P_Id INTEGER NOT NULL,
   Super_Id INTEGER NOT NULL,

   PRIMARY KEY(P_Id),
   CONSTRAINT FK_SuperId FOREIGN KEY (Super_Id)
   REFERENCES Employees(P_Id)
);

answered Mar 27 '18 at 23:03

Eray Balkanli

7,752
11
48
82

reference a foreign key to a primary key within the same table

1 Answers1