CODEWITHSUNDEEP
mysqldatabase
PRO
PRO

Reputation: 21

how to add foreign key to table that reference to column of data type blob

My problem: i created two tables using mysql the first table called team and it has team id and team name the second table media table and it has id and image of type blob i want to add foreign key in table team refernce to column image in media table i searched google but i found that i can not do this way so if i can not is there any way to add a foreign key reference to column that hold image data?

this is the team table

CREATE TABLE `team` (
  `TE_ID` INT(11) NOT NULL,
  `TE_Name` VARCHAR(255) NOT NULL,
  `image` VARCHAR(255) NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

and this media table

CREATE TABLE `media`(
`media_id` INT(11) NOT NULL,
`media_jpg` BLOB
);ENGINE=INNODB DEFAULT CHARSET=utf8;

i hope someone can help me

Upvotes: 0

Views: 398

Answers (1)

The Impaler
The Impaler

Reputation: 48780

As a general rule keys should be small and easy to use, reference, etc. Don't use the BLOB as a foreign key, but use its ID instead. For example:

CREATE TABLE `media`(
  `media_id` INT(11) NOT NULL,
  `media_jpg` BLOB
);ENGINE=INNODB DEFAULT CHARSET=utf8;

Then, create the table that references this one:

CREATE TABLE `team` (
  `TE_ID` INT(11) NOT NULL,
  `TE_Name` VARCHAR(255) NOT NULL,
   image_id INT(11) NOT NULL,
   constraint fk1 foreign key (image_id) references `media` (`media_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

Also, you don't need to use backticks to enclose every column name. Use them only when you have names with spaces or special characters. It doesn't seem to be the case in any of your table or column names.

Upvotes: 1

Related Questions