asked on

Reference a non-keyed column

Is it possible to create a table which has a column (A) which references a column (B) in another table which is not a key. I want to make sure that each value in A contains a value found in B. I suppose a many-to-many relationship.

I have done this in MS Access before but I'm not sure how to construct the SQL statement to create the table. I tried using foreign key, which didn't work because B is not a key.

BrandonGalderisi

If you mean references as in foreign key, no. You need to reference either a primary or unique index/constraint when creating a foreign key. But as far as joining the tables in a select statement, yes. You can join two tables together that do not have a PK/UNIQUE index on the parent table.

bejhan

ASKER

I don't really need to reference a foreign key. I have created the table Recipes (the first SQL statement). Now I want to create a table MealPlans (the second SQL statement) and I want to put the constraint that RecipeID in MealPlans must be a RecipeID found in Recipes (hence the Many-To-Many relationship).

CREATE TABLE Recipes(ID INTEGER, RecipeName CHAR(50) NOT NULL, IngredientID INTEGER NOT NULL, IngredientAmount FLOAT, PRIMARY KEY(ID, IngredientID), FOREIGN KEY(IngredientID) REFERENCES Ingredients(ID))
 
CREATE TABLE MealPlans(ID Integer, MealPlanName CHAR(50) NOT NULL, RecipeID INTEGER, PRIMARY KEY(ID, RecipeID)

Open in new window

BrandonGalderisi

Ok. You want a foreign key for that with recipeid referencing recipies.id

alter table MealPlans
add constraint FK_MealPlans_Recipe
foreign key (RecipeId)
references Recipes (ID)

That's not really a MANY TO MANY since each MealPlans.ID will only reference ONE and only ONE Recipes (according to your data model.

ASKER CERTIFIED SOLUTION

dportas

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

bejhan

ASKER

I figured as much. I know that the construction of the Recipes table is not the best (I should have a definition table too), just thought I would cut down on tables since this is an embedded db. But you're right I shouldn't be taking shortcuts like this (storing name multiple times is no good).