Same Problem On Foreign Key

[Luc_40]

CREATE TABLE artists (

artist_id INTEGER UNSIGNED AUTO_INCREMENT

NOT NULL PRIMARY KEY,

artist VARCHAR (32) NOT NULL UNIQUE

) type=InnoDB;

CREATE TABLE albums (

album_id INTEGER UNSIGNED AUTO_INCREMENT

NOT NULL PRIMARY KEY,

album VARCHAR (32) NOT NULL,

year YEAR

) type=InnoDB;

CREATE INDEX by_album ON albums(album);

CREATE TABLE genres (

genre_id SMALLINT NOT NULL PRIMARY KEY,

genre VARCHAR (32) NOT NULL

) type=InnoDB;

CREATE TABLE tracks (

track_id INTEGER UNSIGNED AUTO_INCREMENT

NOT NULL PRIMARY KEY,

artist_id INTEGER UNSIGNED NOT NULL,

album_id INTEGER UNSIGNED NOT NULL,

track SMALLINT,

title VARCHAR (32) NOT NULL,

genre_id SMallint null,

comment VARCHAR (32) NOT NULL DEFAULT '',

filename VARCHAR (255) NOT NULL,

INDEX (artist_id),

INDEX (album_id),

INDEX (genre_id),

FOREIGN KEY (artist_id) REFERENCES artists(artist_id),

FOREIGN KEY (album_id) REFERENCES albums(album_id),

FOREIGN KEY (genre_id) REFERENCES genres(genre_id)

) type=InnoDB;

CREATE UNIQUE INDEX album_tracks ON tracks(album_id, track);

CREATE INDEX by_title ON tracks(title);

These are all my tables. When i put some data in artists and albums but nothing in genres, and i will try to put some data in tracks, and i leave the field genre_id blank (it can be null) then i get an error 'cannot add or update child record …'

Solution ?

[peterlaursen]

well .. this is basically how a Foreign Key is supposed to work! A Foreign Key also is a CONSTRAINT. Data in the child table are constrainted to/restricted to being valid with data in the parent table.

A Foreign Key is an index where the index in being built with data from another table – You cannot use a NULL value is an Foreign Key. Also NULL is nothing – it is not zero! That is why you get the error 'Can't update …' – because there is nothing (that is what NULL means) to update with.

Further you define genre_id SMALLINT NOT NULL PRIMARY KEY and write and i leave the field genre_id blank (it can be null). Is not that a contracition?

I believe that you must define a certain value for album_id, artist_id and genre_id having the meaning of 'unknown/undecided'. That can be O (zero) -1 or anything.

I also hope you understand that the question(s) you are asking are not SQLyog-related questions – but general MySQL/database design/SQL questions. As such they should probably have been in 'MySQL Server' category. Just for perfectness! But that is OK 🙂

However there a other places to ask database design questions where you will probably get more response.

However Cal is here now I see …

[Luc_40]

peterlaursen wrote on Jan 10 2006, 02:41 PM:

Further you define genre_id SMALLINT NOT NULL PRIMARY KEY and write and i leave the field genre_id blank (it can be null).  Is not that a contracition?

I meant the field genre_id in the table tracks, there it is said to be null allowed.

[Author]

Posts