0

Example questions I am following, created tables this way and was able to insert value for ID as null, since it is auto increment, I thought it would do the math itself? Am I misunderstanding the auto increment feature? How come this doesn't work? Included full code below.

create database music;

use music;

create table Artist(
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30)
);
create table Album(
id INT PRIMARY KEY AUTO_INCREMENT,
    release_date DATE,
    name VARCHAR(30),
    genre VARCHAR(30),
    artist_id INT,
    FOREIGN KEY(artist_id)
    REFERENCES Artist(id)
);

create table Song(
id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(3),
    length_sec INT,
    album_id INT,
    FOREIGN KEY(album_id)
    REFERENCES Album(id)
);

# NULL NOT WORKING FOR ID????

INSERT INTO Artist VALUES(null, 'Rihanna');
INSERT INTO Album VALUES(null,current_date(),'SUPERBOWL','POP',null);
INSERT INTO Song VALUES(null,'S&M',180,null);

I tried to assign integer values to ID and it worked that way! As below:

INSERT INTO Artist VALUES(1, 'Rihanna');
INSERT INTO Album VALUES(2,current_date(),'SUPERBOWL','POP',1);
INSERT INTO Song VALUES(3,'S&M',180,2);
Shadow
  • 33,525
  • 10
  • 51
  • 64
gingeryaz1402
  • 1
  • 2
  • @paulsm4: With `null` it works too, see: https://dbfiddle.uk/AxPoWO41 – Luuk Feb 26 '23 at 18:44
  • not reproducible, see: https://dbfiddle.uk/ZRSG0lzW – Luuk Feb 26 '23 at 18:48
  • Even using `0` for the id works, see: https://dbfiddle.uk/ppDri7cR and [MySQL is not failing when deliberately inserting `NULL` in Primary Key AUTO_INCREMENT column](https://stackoverflow.com/questions/27854266/mysql-is-not-failing-when-deliberately-inserting-null-in-primary-key-auto-incr) – Luuk Feb 26 '23 at 18:56
  • More stuff worth studying.... (https://dbfiddle.uk/EqP402qg) – Luuk Feb 26 '23 at 18:59
  • 2
    If you want the database to automatically assign the ID, then "best practice" is to omit that field from your insert. This syntax (unlike "0" or "null") is portable to DB2, PostgreSQL, MSSQL, and Oracle. – paulsm4 Feb 26 '23 at 19:13
  • @paulsm4 I get this error when I try that: Error Code 1136. Column count doesn't match value count at row 1. I was told that this may happen as my teacher says you must specify for each column in the INSERTs. Thoughts? – gingeryaz1402 Feb 26 '23 at 19:32
  • @Luuk That looks good! How come it works with you but I get an error? Mine is also version 8.0 (8.0.32) – gingeryaz1402 Feb 26 '23 at 19:34
  • Recommended syntax: `INSERT INTO Artist (name) VALUES('Rihanna');` – paulsm4 Feb 26 '23 at 21:11
  • You mentioned an error, which only people with a crystal ball can see it. – Georg Richter Feb 26 '23 at 21:33

0 Answers0