CodeIgniter Forums

Full Version: which DB columns are autoincrement
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
It could be useful to have a database-agnostic way of determining which columns are autoincrement, ie. what column does the insert_id value returned from DB inserts belong to
As pointed out by @superior in the CodeIgniter manual. there is an exception for not wanting autoincrement of the "id" in a table, other than that, the rule is to create the table with the autoincrement option enabled.
id is auto increment and no null
My apologies, it is evident that I must not have understood clearly what you want, because the optimization of any column of a table is as much possible in its creation as it is already in use.
Likewise, the database-agnostic quote left me somewhat confused. What exactly is a database-agnostic?

Dear @paliz, I'm talking about MySql and/or Mariadb. If you don't define that any column field is not autoincrement it will not be! I'm not required to have an "id" field, it could be eg name_id.
Code:
CREATE TABLE songs (
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  album_id int UNSIGNED NOT NULL,
  name_id int UNSIGNED NOT NULL,
  disc int(10) DEFAULT '1' NULL,
  performer VARCHAR(128) COLLATE utf8mb4_swedish_ci NULL,
  composer VARCHAR(128) COLLATE utf8mb4_swedish_ci NULL,
  trk int(3) DEFAULT '1' NOT NULL,
  hint int(3) DEFAULT '0' NULL,
  song VARCHAR(128) COLLATE utf8mb4_swedish_ci NOT NULL,
  time VARCHAR(10) NOT NULL,
  created_at DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  update_at DATETIME on update CURRENT_TIMESTAMP NULL DEFAULT NULL
) ENGINE = InnoDB;
Read this article.

MySQL - 3.6.9 Using AUTO_INCREMENT

That should clarify it.
"database-agnostic" means I don't need to care whether the database is mysql, postgre, sqlite, etc. For example the getFieldData() function in codeigniter is database-agnostic: you can use it the same way regardless of what database system your application uses as long as codeigniter implements the code required by each database system.

For mysql specifically, getFieldData() uses a "SHOW COLUMNS FROM ..." query which returns also the information which columns are autoincrement, but this is not used in codeigniter

Also note that in mysql an autoincrement column doesn't have to be also a primary key column

https://codeigniter.com/user_guide/model...your-model does not solve this problem, it assumes the problem is already solved by a human already knowing which column is autoincrement and manually changing the source code accordingly before running it.

getFieldData() tells you which columns are primary keys, what are their types, max_length, etc without you having to know this information before running the code. It would be nice if it also told us which column is autoincrement.
Thank you very much for your attention. I'm afraid this may not be soon.

I'm an old man, retired, I've worked with mainframes since the late 1960s, it's been over 50 years. You could say that mainframes are the source of everything we have now. Just to give you an idea, in June 1970 IBM launched a new generation of equipment, /370, and among many new developments, there was, for example, the concept of Virtual Machine, which would only be used on microcomputers at the end of the last century .

What we call Schedulling Tools also appeared, they are used to automate production processes, with a reduction in execution time and an error rate of practically zero. However, there is a golden rule for this to happen: the system is only implemented when there is a certain number of people who have mastered the entire manual process, who can intervene in a failure in the automatic process for maximum damage reduction, as well as plenty documentation of the entire process.

Why am I saying this? You quoted the following: "https://codeigniter.com/user_guide/model...your-model does not solve this problem, it assumes the problem is already solved by a human already knowing which column is autoincrement and manually changing the source code accordingly before running it."
In other words, what the manual is saying, in the best scenario, is that it is assumed that a Data Modeling was done in which tables, fields, columns have a reason for being, etc., which suggests the existence of individuals with full knowledge of this part of what will become a set of steps, an application.

In this area of ​​frameworks, PHP and everything related to microcomputers, I'm a beginner, an amateur, but I'm lucky to have a theoretical and practical basis that help me, a lot.
I have a private, local website, where I avoid being in an absolute state of idleness.

ATTENTION: all this does not mean that I am right and you are wrong and vice versa, only that we think differently.

And in relation to database-agnostic, I think the most appropriate metaphor is database-laic, which recognizes and allows the existence of different technologies, both in essence, philosophy and goals, in perfect harmony. But that, who knows, is for another time.

Thanks for your patience and understanding.
Hugs.
Hope you did not drop a stack of puch cards.