1:1 how to ? - yes, only this. :s

Hello all,

This is a very basic question but I don’t even know how to search for it.

We have two tables:

Images_Logo table - that will have the logo images and related information inside.

Associations table - that will have the associations info like name, address…

It makes no sense to store images specific info on the associations table.
So how can we do a 1:1 relationship here?

We do it at creating time? Or when we want to retrieve the information?

Are we forced to use innodb or we can use myISAM, or, are we force to use foreign keys or can we use joins instead ?


foreign keys or joins is not an “either or” situation – use foreign keys if you want the database to enforce relational integrity

please explain why you think it makes “no sense” to store logo image information in the associations table


So that when we remove one the other gets removed or when we update one the other gets updates, or, when we try to remove the other doesn’t let us doing it…

Because associated with the images storage we will have other informations like, filename, mime_type, file_size, file_data … and those informations don’t belong to Associations hence, they seem to be more related with Images or files, exclusively.


excellent answer

okay, to make a 1:1 relationship, put a foreign key into the logos table, to reference the associations table

Ok… on the last hours (yes hours! :blush:) I’ve come up with something like this:

CREATE TABLE association
    id_association INT not null PRIMARY KEY AUTO_INCREMENT,
    name_association varchar(255) not null,
    address_association text,
    post_cod varchar(50),
    telephone_association varchar(100),
    email_association varchar(100),
    url_association varchar(255),
    index (name_association)
CHARACTER SET utf8 COLLATE=utf8_general_ci,

create table image
    id_image serial PRIMARY KEY,
    name_image varchar(255) not null,
    mimeType_image varchar(255) not null,
    fileSize_image int not null,
    fileData_image longblob not null,
    id_association INT,
    FOREIGN KEY(id_association) REFERENCES association (id_association) on delete cascade,
    index (name_image)
CHARACTER SET utf8 COLLATE=utf8_general_ci,

Is this ok?

If so, please advice:
When we delete an image, the correspondent association should NOT be deleted. By taking no options on that, we are assuming this procedure?

Thanks a lot,

you did it right :slight_smile:

in this relationship, the refencing table is the logos, and the referenced table is the associations

deleting a referencing row is always possible, and never has any effect on the referenced row

you can delete a logo if you wish, but the association will stay

the ON DELETE and ON UPDATE options, which are specified in the referencing table, refer to delete/update actions taken on the referenced table

so if you delete an association, or change an association’s id number (although with an auto_increment, you would never need this), then you have options as to what to do to any related logos

Getting crazy:
So this is like triggers (that I’ve heard so many times here and there) that are inside this innoDB engine?

When we delete on table A, it deletes the corresponded record on table B,
just by interpreting those lines:

WHERE column = 'somecolumnvalue'


I it’s too absurd I will leave it for the future, np.


yup, that’s what ON DELETE CASCADE will do – if you delete an association, its logo is also automatically deleted

why wouldn’t you want that?

do you wish to have logos in your database for associations that don’t exist?

Not at all. :slight_smile: I’m finding all this quite cool. Long live innoBD :slight_smile:

Thanks for your time once again :slight_smile:

I’m sorry to repost here, but it is so related that I found useless to create a new post:

Regarding this 1:1 relation here, I’m facing a hard time trying to understand how will the insert work here.

At the same time we insert something on Associations a corresponded Image should be inserted into the Image table.

On the logical part, I can have two inserts one after the other.
The first puts the data into Associations and the second, inserts an Image.

But… and those are my questions:
What if one of the tables, for some reason, doesn’t work - we could have something on the Associations table, and nothing on Image table, supposing the image one was the table that didn’t work for some reason.

What if two users, at different points, for a bizarre coincidence, are trying to insert data at the same time, then it could be the case that we have Two associations but with switched logos. :s

What should we do on those cases?

If those cases are very Very rare (I’m talking about a very little database here), then using one insert after another is the way to go?

Update: Why do I need those inserts to happen almost at the same time?
I’m assuming that, for retrieving the association and the correspond logo, I would join both tables where the id of one is equal to the fk of another, hence, when we insert (or delete) the id’s should be synchronized.
I’m not getting this right perhaps… :s

Thanks a lot,

You’re using an autoincrement key field in your associations table. Assuming that you’re using PHP, you’ll use mysql_insert_id() to retrieve the ID generated for the AUTO_INCREMENT column by the previous query. It will give the id generated by the previous query in that session, not the last one generated by another session.
I guess other languages will have a similar function.

I see.

So in that session is something we should be aware at. ok.

I will try to use this, since I’m on PDO:

Thanks a lot,

Maybe session isn’t the right word. If you read the php manual page I linked to, I’d say it is limited to a single MySQL connection.

And perhaps we can even use mysql function and leave all to mySQL no?


Thanks again,