Why does the default constraint not work in mysql?

Question

Error Code: 1136. Column count doesn't match value count at row 1

roll_no INT PRIMARY KEY NOT NULL unique,
name CHAR(15) NOT NULL,
class integer(3),
section CHAR(1),
sibling boolean default false);

insert into school
values(1,'ramesh',12,'D');

Usually in these scenarios you'd specify the primary key as an auto increment column. You'd still have to explicitly set the other column names. — erik258, Oct 31 '20 at 13:43
`PRIMARY KEY NOT NULL unique` either PRIMARY KEY or UNIQUE, having them both does not improve anything — Lukasz Szozda, Oct 31 '20 at 13:45

score 3 · Answer 1 · answered Oct 31 '20 at 13:27

3

If you don't provide values for all the columns, you need to explicitly state what columns the values refer to:

insert into school(roll_no, name, class, section)
values(1, 'ramesh', 12, 'D');

answered Oct 31 '20 at 13:27

Mureinik

297,002
52
306
350

What if i had to enter the rows: (1,'ramesh',12,'D'),(2,'Sam',11,'C',True) – AlphaM Nov 01 '20 at 15:43

score 2 · Answer 2 · answered Oct 31 '20 at 13:27

You didn't specify the column names in the insert, and therefore MySQL is expecting data for all columns. Qualify your target column names, and the insert should work:

INSERT INTO school (roll_no, name, class, section)
VALUES
    (1, 'ramesh', 12, 'D');

Best practice for SQL inserts is to always list out the target columns for the insert. One reason for this is that, if you don't, an insert statement could break if either a) the number of columns changes, or b) the order of columns in the table changes.

Why does the default constraint not work in mysql?

2 Answers2