[Solved] INSERT INTO with Auto increment

Discuss the database features

[Solved] INSERT INTO with Auto increment

Postby vallyxit » Tue Oct 17, 2017 11:05 pm

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
vallyxit
 
Posts: 3
Joined: Tue Oct 17, 2017 11:02 pm

Re: INSERT INTO with Auto increment

Postby Villeroy » Tue Oct 17, 2017 11:15 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24594
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: INSERT INTO with Auto increment

Postby vallyxit » Tue Oct 17, 2017 11:18 pm

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
vallyxit
 
Posts: 3
Joined: Tue Oct 17, 2017 11:02 pm

Re: INSERT INTO with Auto increment

Postby Villeroy » Tue Oct 17, 2017 11:39 pm

'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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24594
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: INSERT INTO with Auto increment

Postby vallyxit » Tue Oct 17, 2017 11:48 pm

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
vallyxit
 
Posts: 3
Joined: Tue Oct 17, 2017 11:02 pm


Return to Base

Who is online

Users browsing this forum: No registered users and 9 guests