Page 1 of 1

[Solved] INSERT INTO with Auto increment

Posted: Tue Oct 17, 2017 11:05 pm
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?

Re: INSERT INTO with Auto increment

Posted: Tue Oct 17, 2017 11:15 pm
by Villeroy
Insert Null into the auto-incrementing field.

Re: INSERT INTO with Auto increment

Posted: Tue Oct 17, 2017 11:18 pm
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

Re: INSERT INTO with Auto increment

Posted: Tue Oct 17, 2017 11:39 pm
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.

Re: INSERT INTO with Auto increment

Posted: Tue Oct 17, 2017 11:48 pm
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'
);