Foreign Key confusion

Question

I am new to database and making a gym management system, I implemented a database from the tutorials on www.homeandlearn.co.uk. I have completed the project without foreign keys. Now I have to link tables but I am getting this error:

Update cannot proceed due to validation errors.
Please correct the following errors and try again.

SQL71516 :: The referenced table '[dbo].[member_info]' contains no primary or candidate keys that match the referencing column list in the foreign key. If the referenced column is a computed column, it should be persisted.

I do not know what is this error about. Please tell me how to fix this? Do i have to create a new database now or I can still use the foreign keys in the same database? I am using Visual Studio 2012. All help will be appreciated. Thanks in advance. Cheers,

I do have a primary key, and I have set it to increment by 1. see this is my table.

CREATE TABLE [dbo].[member_info] (
[Id]                INT           IDENTITY (1, 1) NOT NULL,
[memberName]        NVARCHAR (50) NULL,
[father_name]       NVARCHAR (50) NULL,
[age]               NCHAR (10)    NULL,
[address]           NVARCHAR (50) NULL,
[contact]           NVARCHAR (50) NULL,
[height]            NVARCHAR (50) NULL,
[weight]            NVARCHAR (50) NULL,
[chest]             NVARCHAR (50) NULL,
[triceps_biceps]    NVARCHAR (50) NULL,
[waist]             NVARCHAR (50) NULL,
[shoulders]         NVARCHAR (50) NULL,
[thighs]            NVARCHAR (50) NULL,
[calves]            NVARCHAR (50) NULL,
[instructor]        NVARCHAR (50) NULL,
[date_of_admission] DATE          NULL,
[photo]             IMAGE         NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
CONSTRAINT [FK_member_info_instructor_info] FOREIGN KEY ([instructor]) REFERENCES
[instructor_info]([instructor])
);

This is my member_info and below is my instructor_info table:

CREATE TABLE [dbo].[instructor_info] (
[InstructorID]      INT            IDENTITY (1, 1) NOT NULL,
[instructor]        NVARCHAR (50)  NULL,
[father_name]       NVARCHAR (50)  NULL,
[age]               NCHAR (10)     NULL,
[address]           NVARCHAR (MAX) NULL,
[contact]           NVARCHAR (50)  NULL,
[height]            NCHAR (10)     NULL,
[weight]            NCHAR (10)     NULL,
[chest]             NCHAR (10)     NULL,
[triceps_biceps]    NCHAR (10)     NULL,
[waist]             NCHAR (10)     NULL,
[shoulders]         NCHAR (10)     NULL,
[thighs]            NCHAR (10)     NULL,
[calves]            NCHAR (10)     NULL,
[memberName]           NVARCHAR (50)  NULL,
[date_of_admission] DATE           NULL,
[photo]             IMAGE          NULL,
PRIMARY KEY CLUSTERED ([InstructorID] ASC)
);

Answer 1

You have to create primary keys for your tables in order to reference them in your foreign keys. Below an example of creating a table with a primary key, so you don't stumble on this error later on.

CREATE TABLE member_info (
 id MEDIUMINT NOT NULL AUTO_INCREMENT,
 name CHAR(30) NOT NULL,
 PRIMARY KEY (id)
) ENGINE=MyISAM;

Link for MYSQL documentation on this: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

EDIT: since my answer was posted, the tag changed from mysql to mssql, as well as a snippet to provide more info. For history purposes, I'm adding the code below to answer the new question.

CREATE TABLE [dbo].[instructor_info] (
[InstructorID]      INT            PRIMARY KEY IDENTITY (1, 1) NOT NULL,
[instructor]        NVARCHAR (50)  NULL,
[father_name]       NVARCHAR (50)  NULL,
[age]               NCHAR (10)     NULL,
[address]           NVARCHAR (MAX) NULL,
[contact]           NVARCHAR (50)  NULL,
[height]            NCHAR (10)     NULL,
[weight]            NCHAR (10)     NULL,
[chest]             NCHAR (10)     NULL,
[triceps_biceps]    NCHAR (10)     NULL,
[waist]             NCHAR (10)     NULL,
[shoulders]         NCHAR (10)     NULL,
[thighs]            NCHAR (10)     NULL,
[calves]            NCHAR (10)     NULL,
[memberName]        NVARCHAR (50)  NULL,
[date_of_admission] DATE           NULL,
[photo]             IMAGE          NULL
);

CREATE TABLE [dbo].[member_info] (
[Id]                INT           PRIMARY KEY IDENTITY (1, 1) NOT NULL,
[memberName]        NVARCHAR (50) NULL,
[father_name]       NVARCHAR (50) NULL,
[age]               NCHAR (10)    NULL,
[address]           NVARCHAR (50) NULL,
[contact]           NVARCHAR (50) NULL,
[height]            NVARCHAR (50) NULL,
[weight]            NVARCHAR (50) NULL,
[chest]             NVARCHAR (50) NULL,
[triceps_biceps]    NVARCHAR (50) NULL,
[waist]             NVARCHAR (50) NULL,
[shoulders]         NVARCHAR (50) NULL,
[thighs]            NVARCHAR (50) NULL,
[calves]            NVARCHAR (50) NULL,
[instructor]        INT           FOREIGN KEY REFERENCES instructor_info(InstructorID),
[date_of_admission] DATE          NULL,
[photo]             IMAGE         NULL
);

Answer 2

This is your table instructor_info

[dbo].[instructor_info] 
PRIMARY KEY CLUSTERED ([InstructorID] ASC)

So if you want to reference that primary key from your table member_info, you must reference that exact column name (InstructorID).

So your current FK constraint won't work - you need to reference that column name, and you must use the same datatype.

Change your table member_info to use

[Instructor_ID] INT

(instead of the [instructor] NVARCHAR(50) column) and then change your FK constraint to:

CONSTRAINT [FK_member_info_instructor_info] 
FOREIGN KEY ([instructor_ID]) 
REFERENCES [dbo].[instructor_info]([Instructor_ID])

Any foreign key in a table must reference the other table's primary key (or a unique constraint) - it cannot just reference any column you like....

Answer 3

For you to have a foreign key referencing a table you must have a primary key on that table. Check your member_info table and make sure there is one.