Database Primary Key Naming

Databases
#1

Hi all,

I’m just wanting to get any views/opnions regarding a database definition question.

I am using SQLite for a small number of tables and in each one I have a primary key field which is defined as Integer, PrimaryKey, AutoIncrement, Not Null, Unqiue (I have always used this definition without issue).

Is it considered OK / reasonable to name the field ‘PK’ or ‘PKID’ as opposed to say ‘AccountPK’ or ‘AccountPKID’ ?

My preference would be to name it ‘PKID’ but is there any disadvantage to this, or any best practise overall for naming primary key fields ?

Thanks.

#2

We usually use such kind of name for indexes. PK_name for primary keys, UK_name for unique keys, IX_name for any indexes, UX_name for unique indexes. The usual unique, autoincrement, primary key field is known as Identity Field, and usually gets the simple name of “id”.

#3

When you have 2 tables, like “sale” and “item”, we simply refer them as SELECT sale?id, item?id …

#4

Interesting.

If I use ‘Company.ID’ and ‘Account.ID’ it fails to find the column.

If I use ‘CompanyID’ and ‘AccountID’ it works just fine.

Edit : It works if I enclose the column name with backticks i.e Company.ID, but not if they are enclosed with single or double quotes.

#5

– Tested. It works as usual

CREATE TABLE “company” (
“id” INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
“name” TEXT
);

BEGIN;
INSERT INTO “company” VALUES (NULL, ‘Comp One’);
INSERT INTO “company” VALUES (NULL, ‘Comp Two’);
COMMIT;

CREATE TABLE “account” (
“id” INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
“name” TEXT,
“id_company” INTEGER,
CONSTRAINT “fk_company_id” FOREIGN KEY (“Id_company”) REFERENCES “company” (“id”) – Foreign Key, links data in another table
);

BEGIN;
INSERT INTO “account” VALUES (NULL, ‘acc 001’, 1); – This account belongs to company?id = 1
INSERT INTO “account” VALUES (NULL, ‘acc 002’, 1);
COMMIT;

SELECT
account?id AS account_id,
account?name AS account_name,
company?id AS company_id,
company?name AS company_name
FROM account
JOIN company ON account?id_company = company?id

– The above text uses a fake dot “?” (&u2024) instead of “.” (&u002E) to allow ttt?id instead of ttt.id
– So don’t just copy/paste, change the dots too

#6

Makes no sense to me. It should be one of those for SQLITE:

SELECT “account”.“id” FROM account – Quoted, dot outside

SELECT account?id FROM account – Pure, as no special char or spaces are used

In both cases, the exported column name is just “id”.

When mixing tables in a select, exported names must be aliased to be clear, using: AS whatever_good_name

#7

Apostrophes " ’ " designs strings. So…

SELECT ‘ok’ AS field_ok FROM ok_table

Which table contains a column named “ok”, will return the string constant ‘ok’ named as field_ok, and not the column contents

#8

Try: Company_ID’ and 'Account_ID.

Company.ID looks to me as TABLE COMPANY, field ID

But, this is just me.

what said sqlite.com ?

#9

as xojo does not return the name of the table when you make a sqlselect in a recordset,
I prefer to name my pk as id_tablename instead of just id
if you have lots of joined tables, it’s easier to get the right id in your recordset by its name,
or you have to alias all of them in the query to get them back from the recordset otherwise.

#10

I’'m wondering if this has something to do with SQLite Manager which is what I use to create my tables.

https://imgur.com/a/4hlEDgE

#11

Creating a TABLE by code is easy.

An example is in Database.SQLExecute .

The FieldType (TEXT is used in the above example) are defined in SQLiteDatabase .

Sk if you have troubles writing your CREATE TABLE string.

#12

I used StockID, ClientID when it need to be use else where on another table… and recordkey for another sub tables.

tblStock - StockID, Artist, Title, Status
tblStockExpense - Recordkey, StockID, ExpenseCategory, Amount, Date

#13

Don’t put the dot IN the field name definition. The dot is used the address the field referencing its table in a SQL query, not in the definition.
I, would use in your example, the names: id (not account?pkid, nor account?id or even pkid), description, and display_order

In a SQL QUERY you optionally refer a field by its more complete selector in the DBMS namespace, like, SELECT mytable.myfield when mixing multiple databases or tables in a query.

Another usual convention is using lowercase names separated by underlines instead of CamelCase we use programming.

#14

[quote=391649:@Rick Araujo]Don’t put the dot IN the field name definition. The dot is used the address the field referencing its table in a SQL query, not in the definition.
[/quote]

I think this was the root of the evil. I was using the DOT as part of the column name, which was confusing the SQL as a few posters alluded to

I’ve switched it away to using just ‘id’ on its own now, and using lowercase for the rest of the column names too.

Thanks everyone for the input, much appreciated.

#15