CODEWITHSUNDEEP
mysqldatabase
Aisiri Shankar
Aisiri Shankar

Reputation: 115

Unable to insert into database despite specifying a default constraint on column

I have the following table in MySQL database:

mysql> describe student; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | name | varchar(50) | YES | | NULL | | | dob | date | YES | | NULL | | | reg_no | varchar(20) | NO | PRI | NULL | | | department | varchar(50) | YES | | NULL | | | branch | varchar(50) | YES | | NULL | | | semester | int(11) | YES | | 1 | | +------------+-------------+------+-----+---------+-------+ 6 rows in set (0.01 sec)

When I try to insert into this database without specifying the last value (i.e.semester), it results in an error
mysql> insert into student values('John Smith','1990-01-01','123ABC','Chemistry','Organic Chemistry'); ERROR 1136 (21S01): Column count doesn't match value count at row 1

Why is this not working? Isn't that the whole point of having a default constraint? That the value specified as default gets assigned if it isn't present in the insert query.

If I write the query like this, it works:
mysql> insert into student values('John Smith','1990-01-01','123ABC','Chemistry','Organic Chemistry',DEFAULT); Query OK, 1 row affected (0.01 sec)

What is happening? Is my syntax wrong? I would like to be able to insert the record without having to specify 'DEFAULT'

Upvotes: 2

Views: 90

Answers (2)

ramana
ramana

Reputation: 48

your syntax is wrong while inserting default values,so instead of that query use insert into student(name,dob,reg_no,department,branch)values('John Smith','1990-01-01','123ABCF','Chemistry','Organic Chemistry');

Upvotes: 0

smb564
smb564

Reputation: 353

If you are not adding values to all the table columns you should specify which columns you add data.

Example:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

So in your case,

INSERT INTO student(name, dob, regno, department, branch)
    VALUES ('John Smith','1990-01-01','123ABC','Chemistry','Organic Chemistry');

Upvotes: 4

Related Questions