ERROR 1215 (HY000): Cannot add foreign key constraint

[aslam.junaid786]

CREATE TABLE Artist
(
A_id INT NOT NULL,
A_song varchar(100),
A_name varchar(100),
PRIMARY KEY(A_id,A_song)
);
CREATE TABLE Instrument
(
I_id int NOT NULL,
I_keys varchar(100),
I_name varchar(100),
PRIMARY KEY(I_id,I_name),

a_id int NOT NULL,
FOREIGN KEY (a_id) references Artist(A_id)
);


CREATE TABLE Song
(
S_id int NOT NULL PRIMARY KEY,
S_vocalist varchar(100),
S_nom int NOT NULL,

S_name varchar(100),
FOREIGN KEY (S_name) references Artist(A_song),
S_inst varchar(100),
FOREIGN KEY (S_inst) references Instrument(I_name),
a_id int NOT NULL,
FOREIGN KEY (a_id) references Artist(A_id)
);

I am getting this error: "ERROR 1215 (HY000): Cannot add foreign key constraint" probably on this part of the code :

  S_inst varchar(100),
    FOREIGN KEY (S_inst) references Instrument(I_name),

Please solve.

[diafol]

Why have you got: A_id AND A_song as your PRIMARY KEY (artists table) and I_id AND I_name as your PRIMARY KEY (instruments table)?

Just use the IDS - no need for varchar indexes and FK on those. Your songs should just utilise the IDs

Anyway, the schema doesn't make much sense to me. The relationships don't seem to work and you'd need IMO one or more link tables.

[diafol]

Sir, Is there any way of linking attributes from one table to he other without using foreign keys?

By linking attributes, I assume you mean joining fields. Yes you can certainly join fields without the need for foreign keys. However, using constraints (foreign keys) is considered good practice.

More than anything though, your schema looks odd. I just don't get it. Try to think of a table as a 'self-contained entity' (they're not, but just consider it for now):

ARTISTS

artist_id (PK/int)
artist_name (varchar,30)

INSTRUMENTS

instrument_id (PK/int)
instrument_name (varchar,30)
instrument_type (FK/int) [on type_id from INSTRUMENT_TYPES]

INSTRUMENT_TYPES

type_id (PK/int)
type_name (varchar/20), e.g. voice, percussion, strings,...

ARTIST_INSTRUMENTS

ai_id (PK/int)
artist_id (FK/int) [on artist_id from ARTISTS]
instrument_id (FK/int) [on instrument_id from INSTRUMENTS]

The artist_instruments is where all the joining takes place. These are often called link tables and usually just contain rows of numbers. Totally useless by themselves, but tremendously useful when used in conjunction with the other tables.

You can retrieve data from all the tables together if you so wish in just one SQL query using 'joins', e.g. INNER JOIN, LEFT JOIN, RIGHT JOIN.