AUTO_INCREMENT does not work in SoloLearn space

Question

Hi all,

I'm doing the SQL Intermediate course, and I'm having trouble with the AUTO_INCREMENT function. According to the course, this should be the code:

CREATE TABLE PhoneNumbers(
id int NOT NULL AUTO_INCREMENT,
customer_id int,
number varchar(55)
);

This triggers the following error:
ERROR: syntax error at or near "AUTO_INCREMENT"
id int NOT NULL AUTO_INCREMENT,

I've tried to find a solution, and the most common I find is that SoloLearn uses PostgreSQL and not MySQL, and that I should use serial als data type instead of int.

CREATE TABLE PhoneNumbers(
id serial NOT NULL AUTO_INCREMENT,
customer_id int,
number varchar(55)
);

It leads to the same error
ERROR: syntax error at or near "AUTO_INCREMENT"
id serial NOT NULL AUTO_INCREMENT,

Does anyone know how I can make AUTO_INCREMENT work?

My next query would be this:
(There are two values, because the id should get set automatically)

INSERT INTO PhoneNumbers VALUES
(1, '06123456789'),
(1, '0368787965'),
(2, '06987654321'),
(3, '06123498765');

Thanks in advance!

Answer 1

Keetie
Since you mentioned AUTO_INCREMENT, I thought you already reached Identity.

I tried my code in the first playground at the "Introduction" lesson, it works in there too.

Your code:
INSERT INTO PhoneNumbers VALUES

My code:
INSERT INTO PhoneNumbers (customer_id, number) VALUES

You have to tell the engine which field the data goes to.

Answer 2

Keetie
I just noticed you finished all Python courses here.
I would suggest you try the sqlite3 module, writing SQL code inside Python.
https://docs.python.org/3/library/sqlite3.html
https://www.sqlite.org/docs.html

Answer 3

Not sure what's wrong, but can you check, on your end, what happens if we add PRIMARY KEY after the AUTO_INCREMENT specifier?

CREATE TABLE PhoneNumbers(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
customer_id int,
number varchar(55)
);

Would it work that way?

Answer 4

The problem is we don't know which SQL engine SoloLearn is using, and we can only guess they use MySQL or PostgreSQL as they are the popular engines.

To get real practice, it is best to install a SQL engine in your computer, or using an office suit which contains a database application, such as MS Office (Access) or LibreOffice (Base).
Personally, I would choose Base as LibreOffice is free. However, Base use HSQLDB, which is not popular.

Interestingly, I just found out this code works in the course playground, which is similar to HSQLDB syntax. And I tested it in the playground of Intermediate SQL > identity.

CREATE TABLE PhoneNumbers(
id INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 100 INCREMENT BY 1) PRIMARY KEY,
customer_id int,
number varchar(55)
);

INSERT INTO PhoneNumbers (customer_id, number)
VALUES
(1, '06123456789'),
(1, '0368787965'),
(2, '06987654321'),
(3, '06123498765');

SELECT id, customer_id, number
FROM PhoneNumbers
ORDER BY id DESC;

Answer 5

IPANG,

Thank you for the suggestion. PRIMARY KEY didn't work either unfortunately.

I've tried to work with both PRIMARY KEY and FOREIGN KEY as well, since that's also in the part of the course where I am now, but bot don't work like is being told in the course :(

Answer 6

Wong Hei Ming,

Thankyou for all the help!

Thnx for pointing out missing those VALUES in the INSERT query: with your syntax it indeed fixed the ERROR!
It also runs with and without adding PRIMARY KEY, so for me that's also the first time PRIMARY KEY even worked.

As for trying the sqlite3 module, I would like to try, but think that's too difficult for me at this point. I barely now anything about computers or data science at all, and really need the explanations and the practices from courses like this. I really get lost in all those texts, like I don't even know what's meant with 'a seperate server process', or 'porting a database'.
Maybe when I am little more comfortable with the terms, I will get it one day :)

Thanks again!

Answer 7

Sorry about that. I doubt FOREIGN KEY has anything to do with this problem though. Well anyways, you can see that code works in db-fiddle. Still not sure why it shouldn't work here in SoloLearn.

Just keep on learning, this things we stumble upon are common, so no worries cause in time you'll get it :)

https://www.db-fiddle.com/f/oURzBbYWke7p4R9PHXV8ud/0

Answer 8

Keetie
If you are going down the path of SQL and Python, I would suggest these books to you.

Python for Everybody: Exploring Data in Python 3 by Charles R. Severance
You can get this book for free from his website
https://www.py4e.com/book

Beginning Database Design by Gavin Powell
Beginning SQL by Paul Wilton & John Colby
You can purchase them from Amazon, or you can borrow them from a local library.

The first book gives you introduction about Python. The explanations are much informative than SoloLearn. In the later chapter, it has a project to work with sqlite3 with examples. This book is structured for non-programmer, so it is easy to follow.

The second and third book are for who want to design a database from scratch. It teaches the principle of table design, normalization and other.
Also, you can check out these blog from SoloLearn about database (only available on web).
https://www.sololearn.com/blog/what-is-database/
https://www.sololearn.com/blog/what-is-normalization/

Answer 9

Thank you for your answer!

Very interesting to see what you found.

I also use the SoloLearn playground, however I use it from the introduction lesson from the SQL intermediate course, since I unfortunately can't affort the PRO version of SoloLearn at the moment. I copied the syntax you sent into there, but I still get the same problem.

CREATE TABLE PhoneNumbers(
uniquenr int GENERATED BY DEFAULT
AS IDENTITY(START WITH 1 INCREMENT BY 1)
PRIMARY KEY,
customer_id int,
number varchar(55)
);

INSERT INTO PhoneNumbers VALUES
(1, '06123456789'),
(1, '0368787965'),
(2, '06987654321'),
(3, '06123498765');

ERROR: value "06123456789" is out of range for type integer
(1, '06123456789')

(I also tried (START WITH 100 INCREMENT BY 1) as you suggested, but that also doesn't work)

Am I doing something wrong?