i have table called image_detail which corresponds to details of image files on my server. The primary key i'm using to uniquely identify each .jpg image file is an id and the image file name. However, whenever i enter a new record and have a filename that is the same name as another file, mysql still seems to insert this record, can somone tell me why?
UPDATED TABLE as it was giving erros before
CREATE TABLE image_detail (
Id INT NOT NULL AUTO_INCREMENT,
Filename VARCHAR(255) NOT NULL,
Type VARCHAR(255) NOT NULL,
Size TEXT NOT NULL,
Location VARCHAR(255)NOT NULL,
Description TEXT NOT NULL,
PRIMARY KEY(Id, Filename)
) DEFAULT CHARACTER SET utf8;
INSERT INTO image_detail (Id, Filename, Type, Size, Location, Description) VALUES
(1, 'IMG_0075.JPG', '.jpg', '61.9kb','C:\wamp\www\Shanghai_2010\Cheng_Huang_Temple\IMG_0075.JPG', 'Temple area market'),
(2, 'IMG_0075.JPG', '.jpg', '61.9kb','C:\wamp\www\Shanghai_2010\Cheng_Huang_Temple\IMG_0075.JPG', 'Temple area market');
P.S I am using WAMPSERVER
I need the id there because i will need to link this with different cateogries as each image file belongs to a different category.
The primary key is the first two fields and those are different between those two records.
What is the Id field there for? Unless you have a reason for needing multiple copies of the same filename then getting rid of that field will be the easiest way to get what you want.