CODEWITHSUNDEEP
mysqlphpmyadmin
DjKillerMemeStar
DjKillerMemeStar

Reputation: 425

SQL refer a to b and b to a

I'm using PHPMyAdmin (so mySQL) to create this.

I have 2 tables, Album and photo. Of course, a photo can be part of an album and for that I use a foreign key from photo to album.

But now I want to be able to put a photo in the album table so I can use that photo as a cover for my album. I've tried adding a foreign key but that gives me a foreign key constraint.

Here are the tables to help understand what I mean (foto = photo). The red line indicates what I want to achieve.

enter image description here

I'm not that good at SQL so any help is appreciated.

SQL Album:

CREATE TABLE `Albums` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Album_Naam` varchar(255) NOT NULL,
`Aanmaakdatum` datetime NOT NULL,
`FotoID` int(11) DEFAULT '1',
PRIMARY KEY (`ID`),
KEY `FotoID` (`FotoID`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

SQL Photo

CREATE TABLE `Foto` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Foto_Naam` varchar(255) NOT NULL,
`AlbumID` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `AlbumID` (`AlbumID`),
CONSTRAINT `Foto_ibfk_1` FOREIGN KEY (`AlbumID`) REFERENCES `Albums` (`ID`) 
ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1

Upvotes: 0

Views: 89

Answers (1)

Uueerdo
Uueerdo

Reputation: 15961

A foreign key can be made optional by making the referencing field nullable; NULL values in the field do not violate the foreign key constraints enforced on it. This can be used to represent purely optional relations or, as in the case of this question, defer setting the value in semi-cyclic dependencies.

Note that to remove the cover photo from an album, the album will first need it's cover reference to that foto set to another photo, or to null. Similarly, to delete the album, you would need to delete it's Fotos, and so first set it's cover to null.

Upvotes: 1

Related Questions