Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Identity GENERATED ALWAYS: Make insert with NULL or value into identity column to use the identity generated value [CORE5460] #5731

Closed
firebird-automations opened this issue Jan 18, 2017 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Gerhard S (gschaber)

Relate to CORE5463

Could you support inserting rows where the value for the identity column is passed as NULL in order to increment the value automatically?.

Example:
create table testtbl (
id integer generated always as identity (START WITH 0) NOT NULL primary key,
name varchar(15)
);

insert into testtbl values (NULL, 'name1');

This only makes sense, if the column is NOT NULL, I guess. Other database systems such as MySQL, HSQLDB, MariaDB allow that.

I am aware that the following already works, but using NULL is more flexible and simpler than
insert into testtbl (name) values ('name1');

By the way, I am using Firebird in conjunction with LibreOffice 5.3.0RC1.

@firebird-automations
Copy link
Collaborator Author

Commented by: @aafemt

Current behavior match standard definition of "generated by default" semantic. Your request require "generate always" semantic.

@firebird-automations
Copy link
Collaborator Author

Commented by: Gerhard S (gschaber)

GENERATED ALWAYS would be fine for me as well. Does that mean it would be a duplicate of CORE2732?

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

It's not duplicate of CORE2732. CORE2732 is about GENERATED in the context of our COMPUTED.

@firebird-automations
Copy link
Collaborator Author

Commented by: Ann Harrison (awharrison)

Giving a meaning to assigning NULL to a field that disallows nulls when it's an identity that has a sequence is a MySQL nut-case idea on a par with storing 32767 in a 16 bit integer when you actually assigned 10000, or treating 0-FEB-2017 as any day in February or determining table-name case sensitivity by the local file system rules. This is a terrible idea!

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

description: Could you support inserting rows where the value for the identity column is passed as NULL in order to increment the value automatically?.

Example:
create table testtbl (
id integer generated by default as identity (START WITH 0) NOT NULL primary key,
name varchar(15)
);

insert into testtbl values (NULL, 'name1');

This only makes sense, if the column is NOT NULL, I guess. Other database systems such as MySQL, HSQLDB, MariaDB allow that.

I am aware that the following already works, but using NULL is more flexible and simpler than
insert into testtbl (name) values ('name1');

By the way, I am using Firebird in conjunction with LibreOffice 5.3.0RC1.

=>

Could you support inserting rows where the value for the identity column is passed as NULL in order to increment the value automatically?.

Example:
create table testtbl (
id integer generated always as identity (START WITH 0) NOT NULL primary key,
name varchar(15)
);

insert into testtbl values (NULL, 'name1');

This only makes sense, if the column is NOT NULL, I guess. Other database systems such as MySQL, HSQLDB, MariaDB allow that.

I am aware that the following already works, but using NULL is more flexible and simpler than
insert into testtbl (name) values ('name1');

By the way, I am using Firebird in conjunction with LibreOffice 5.3.0RC1.

summary: Insert NULL into identity column with auth generated value => Identity GENERATED ALWAYS: Make insert with NULL or value into identity column to use the identity generated value

@firebird-automations
Copy link
Collaborator Author

Commented by: Gerhard S (gschaber)

Well, I understand that it is formally not so beautiful, but then again, it is simple, and many database systems do it this way, as mentioned.

And I am only referring to number columns, not date, or anything else.

Another example:
https://docs.oracle.com/cd/E17952_01/mysql-5.5-en/example-auto-increment.html
"...If the column is declared NOT NULL, it is also possible to assign NULL to the column to generate sequence numbers...."

Or your own FAQ:
http://www.firebirdfaq.org/faq29/
"...Checking whether http://NEW.ID is NULL is a common way to prevent getting incremented values when you copy data from some other database or import from some other data source. It can be done in many different ways, but this is the most simple approach: if value for column is not supplied - autoincrement it...."
I just want to avoid creating a trigger for each table.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Link: This issue relate to CORE5463 [ CORE5463 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

This feature as reported is not standard complliant. I created CORE5463 quoting relevant part of the standard.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants