[Solved] INSERT INTO with Auto increment

Discuss the database features
Post Reply
vallyxit
Posts: 3
Joined: Tue Oct 17, 2017 11:02 pm

[Solved] INSERT INTO with Auto increment

Post by vallyxit »

Hello all,
I'm trying to insert data into my table with a column that has autoincremented constraint. I've tried to research but I cannot find anything about openoffice base.

my table:

create table borrower(
Library_Card_Number integer Not Null Primary Key,
name varchar (40) Not Null ,
address varchar(40),
Postal_code varchar(20),
Phone_number varchar(20),
mebership_date date,
check(Phone_number like '^[0-9\-\+]{9,15}$')
);

ALTER TABLE "BORROWER" ALTER COLUMN "LIBRARY_CARD_NUMBER" INTEGER GENERATED BY DEFAULT AS IDENTITY;

Insert statement:
Insert into borrower(Library_Card_Number,name,address,Postal_code,Phone_number,mebership_date)
values (' ','Samil Shah','123 Home st','62989','0555121245','2008-02-01'
);

But of course is giving me an error, could you please help me?
Last edited by vallyxit on Tue Oct 17, 2017 11:48 pm, edited 1 time in total.
Openoffice 4 on windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: INSERT INTO with Auto increment

Post by Villeroy »

Insert Null into the auto-incrementing field.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
vallyxit
Posts: 3
Joined: Tue Oct 17, 2017 11:02 pm

Re: INSERT INTO with Auto increment

Post by vallyxit »

Hello Villeroy,
thank you for your answer, I've tried with null but I'm having the two errors below:

Insert into borrower(Library_Card_Number,name,address,Postal_code,Phone_number,mebership_date)
values ('NULL','Samil Shah','123 Home st','62989','0555121245','2008-02-01'
);

5: Wrong data type: java.lang.NumberFormatException: For input string: "NULL"


Insert into borrower(Library_Card_Number,name,address,Postal_code,Phone_number,mebership_date)
values (null,'Samil Shah','123 Home st','62989','0555121245','2008-02-01'
);
6: Check constraint violation SYS_CT_54 table: BORROWER
Openoffice 4 on windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: INSERT INTO with Auto increment

Post by Villeroy »

'NULL' is the literal text 'NULL'.
and message 6 explains it all. Your primary key is a foreign key to another primary key in the BORROWER table. Remove that dependency. You can not have an auto-value on one side which needs to preexist on the other side. This makes no sense.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
vallyxit
Posts: 3
Joined: Tue Oct 17, 2017 11:02 pm

Re: INSERT INTO with Auto increment

Post by vallyxit »

hello Villeroy,
was check(Phone_number like '^[0-9\-\+]{9,15}$') that was not functioning, now it's working properly, without null as well.

Thank you for your time



SOLUTION:

Insert into borrower(name,address,Postal_code,Phone_number,mebership_date)
values ('Samil Shah','123 Home st','62989','0555121245','2008-02-01'
);
Openoffice 4 on windows 10
Post Reply