Why can't I insert these values into the table I added to this database?-CodePudding

I'm new to SQL and learning its basic functions, i've been instructed to create a table and add it to my database, whilst then adding the appropriate values to each field in the table. But I keep getting a syntax error in the line:

INSERT into Weather_db.client_data

Here is my code so far. Let me know what I should change:

Q1.) Using the CREATE TABLE statement, create a table called client_data with fields

CREATE TABLE Weather_db.client_data (
ID int PRIMARY KEY,
First_name varchar(40) NOT NULL, 
Last_name varchar(40),
Nationality varchar(40), 
Age Float Check (age>18))

Q2.) Insert the following records in the database using the INSERT statement

INSERT into Weather_db.client_data 
VALUES ('John', 'S',
'British', 'null'), 
('Peter', 'Jackson', 'null', '20'), 
('Tom', 'W', 'null', '20'), 
('Jack', 'Patrick', 'American', '30');

CodePudding user response：

ID int PRIMARY KEY,

This column definition is legal, but it doesn't have any automatic behavior to generate unique values. So you must supply a value yourself in your INSERT statement, and it's up to you to make sure the value is unique, i.e. it is not already used on any existing row in this table.

MySQL provides an option for integer primary keys to generate new unique values automatically:

ID int AUTO_INCREMENT PRIMARY KEY,

By using this, you don't have to specify a value in your INSERT statement.

You may like to read this manual page for more information: https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

CodePudding user response：

Your statement has a few syntax issues.

Firstly you are not providing values for all the defined columns.

It's also good practice to always specifically list the columns in the insert statement.

You should not be quoting numeric values, or null values.

You should probably define the ID as auto_increment which means the database will supply a default value.

See a working example

CREATE TABLE client_data (
ID int PRIMARY key auto_increment,
First_name varchar(40) NOT NULL, 
Last_name varchar(40),
Nationality varchar(40), 
Age Float Check (age>18));

INSERT into client_data (first_name, last_name, Nationality, Age)
VALUES ('John', 'S','British', null), 
('Peter', 'Jackson', null, 20), 
('Tom', 'W', null, 20), 
('Jack', 'Patrick', 'American', 30);