How to have a unique value in a database?

helpneeded22 · December 16, 2018, 7:43pm

MySQL and PHP newbie here.

Here’s a description of my issue:

I have a table called “Items”. The primary key column for this table is “item_id”

I have a table called “Images”.

“Images” contains a column named “image_item_id” This contains a value that links the image to the “item_id” in the “Items” table.

“Images” contains a column named “image_type”. The value of type will be either “main” or “secondary”.

I only ever want 1 instance of “main” to associated with “image_item_id”. All other instances are to be “secondary”.

I’m not sure how to go about doing this, or even if it is possible.

I’m guessing that if I do this with PHP, then when I add an image with a “image_type” value of “main”, then I would have code that would check all other instances of the matching “image_item_id”, and if their values are “main”, then change them to “secondary”?

I think I have the knowledge to create the code for this, but before I start fumbling my way to make the code, I just wanted to check if there was a better way to do this (either through PHP or with the settings of the MySQL table).

benanamen · December 16, 2018, 7:50pm

You need to create a unique constraint in the DB using both columns.

helpneeded22 · December 16, 2018, 9:23pm

Thank you for the reply, however I’m just a little too inexperienced to understand this.

By using UNIQUE, wouldn’t that prevent me from using “secondary” more than once?

I want to allow multiple values of “secondary” and only one value of “main” per “image_item_id”.

Based on the code below, how would I apply UNIQUE to do this?

CREATE table images (
image_id PRIMARY KEY AUTO_INCREMENT,
image_name VARCHAR(120) NOT NULL,
image_type VARCHAR(15) NOT NULL,
image_mini_id TINYINT
);

JimL · December 16, 2018, 9:50pm

I think I’d just add the main image id to the item table. Easy fix

helpneeded22 · December 17, 2018, 5:16am

Interesting idea. So I would just scrap the “image_type” from the images table, and then as part of my admin page for “items” I would enable a selection from all images linked to that “item_id”, and the selected one would be assigned to a column such as “item_mainImage” on the Items table.

I like the idea, the only annoyance I see though is the back and forth required.
First, on the Item table, I would add a new Item.
Second, on the Images table, I would add images and link them to the Item.
Third, I would go back to the Item table, and select from the linked images and make one the main display image.

While this can work, it doesn’t seem to be the most efficient?

JimL · December 17, 2018, 6:48am

If you’re adding a new item with images with one request then you could just add the images to the db first and then add the item to the db with the main image id. Doesn’t seem that bad

helpneeded22 · December 17, 2018, 5:09pm

You are right. For my ease of understanding in the journey of learning coding, I have been breaking things down into component parts so it’s easier for my old brain to process – I was hung up on using 1 table at a time. Saving the miniature miniature image info at the same time as saving the item info would be totally do-able. Thank you for helping me out this this.