PostgreSQL Developer Best Practices

On Sat, Aug 22, 2015 at 11:15:07AM -0400, Melvin Davidson wrote:

PostgreSQL Developer Best Practices

1. Prefix ALL literals with an Escape
EG: SELECT E'This is a \'quoted literal \'';
SELECT E'This is an unquoted literal';

Doing so will prevent the annoying "WARNING: nonstandard use of escape in a string literal"

This is certainly not Best Practice as the warning is
annoying for a reason.

Best Practice would rather be something along the lines:

Avoid coding in a way that triggers "WARNING:
nonstandard use of escape in a string literal". If you
cannot comply with this rule document your reasons.

Good example:
CREATE TABLE accounts
( accout_id bigint NOT NULL ,

Typo.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

This is certainly not Best Practice as the warning is
annoying for a reason.

Best Practice would rather be something along the lines:

Avoid coding in a way that triggers "WARNING:
nonstandard use of escape in a string literal". If you
cannot comply with this rule document your reasons.

Thanks for the suggestion. For the past few months I've been dealing with
an error log that is filled with these warnings simply because
the developers do not comprehend how to use ( or the requirement to use)
an escape clause.

Good example:
CREATE TABLE accounts
( accout_id bigint NOT NULL ,

Typo.

So noted, I'll correct.

On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert <Karsten.Hilbert@gmx.net>
wrote:

On Sat, Aug 22, 2015 at 11:15:07AM -0400, Melvin Davidson wrote:

PostgreSQL Developer Best Practices

1. Prefix ALL literals with an Escape
EG: SELECT E'This is a \'quoted literal \'';
SELECT E'This is an unquoted literal';

Doing so will prevent the annoying "WARNING: nonstandard use of

escape in a string literal"

This is certainly not Best Practice as the warning is
annoying for a reason.

Best Practice would rather be something along the lines:

Avoid coding in a way that triggers "WARNING:
nonstandard use of escape in a string literal". If you
cannot comply with this rule document your reasons.

Good example:
CREATE TABLE accounts
( accout_id bigint NOT NULL ,

Typo.

Karsten
--
GPG key ID E4071346 @ eu.pool.sks-keyservers.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Melvin Davidson <melvin6925@gmail.com> writes:

Best Practice would rather be something along the lines:

Avoid coding in a way that triggers "WARNING:
nonstandard use of escape in a string literal". If you
cannot comply with this rule document your reasons.

Thanks for the suggestion. For the past few months I've been dealing with
an error log that is filled with these warnings simply because
the developers do not comprehend how to use ( or the requirement to use)
an escape clause.

IMO best practice in this area is "run with standard_conforming_strings = ON".
If you're seeing this warning at all, it's because you aren't doing that,
which means your code is unnecessarily unportable to other DBMSes.
Adopting a coding policy of always using E'' would make that worse.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom,

Thank you for pointing out "run with standard_conforming_strings = ON"..
However, that is NOT the problem.
What is occurring is that the developers are sending strings like 'Mr.
M\'vey',
which, if we set standard_conforming_strings = ON, would, and does, result
in errors and the statement failing,
which is a lot less desirable that a simple warning.

Therefore, I am trying to educate the developers in the proper method of
escaping strings,
instead of loading up the error log with annoying warnings.

On Sat, Aug 22, 2015 at 1:16 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Melvin Davidson <melvin6925@gmail.com> writes:

Best Practice would rather be something along the lines:

Avoid coding in a way that triggers "WARNING:
nonstandard use of escape in a string literal". If you
cannot comply with this rule document your reasons.

Thanks for the suggestion. For the past few months I've been dealing with
an error log that is filled with these warnings simply because
the developers do not comprehend how to use ( or the requirement to use)
an escape clause.

IMO best practice in this area is "run with standard_conforming_strings =
ON".
If you're seeing this warning at all, it's because you aren't doing that,
which means your code is unnecessarily unportable to other DBMSes.
Adopting a coding policy of always using E'' would make that worse.

regards, tom lane

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Sat, Aug 22, 2015 at 1:16 PM, Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>> wrote:

Melvin Davidson <melvin6925@gmail.com <mailto:melvin6925@gmail.com>> writes:

Best Practice would rather be something along the lines:

Avoid coding in a way that triggers "WARNING:
nonstandard use of escape in a string literal". If you
cannot comply with this rule document your reasons.

Thanks for the suggestion. For the past few months I've been dealing with
an error log that is filled with these warnings simply because
the developers do not comprehend how to use ( or the requirement to use)
an escape clause.

IMO best practice in this area is "run with standard_conforming_strings = ON".
If you're seeing this warning at all, it's because you aren't doing that,
which means your code is unnecessarily unportable to other DBMSes.
Adopting a coding policy of always using E'' would make that worse.

regards, tom lane

On 08/22/2015 02:40 PM, Melvin Davidson wrote:

Tom,

Thank you for pointing out "run with standard_conforming_strings = ON"..
However, that is NOT the problem.
What is occurring is that the developers are sending strings like 'Mr. M\'vey',
which, if we set standard_conforming_strings = ON, would, and does, result in errors and the statement failing,
which is a lot less desirable that a simple warning.

Therefore, I am trying to educate the developers in the proper method of escaping strings,
instead of loading up the error log with annoying warnings.

Please dont top post.

But you are not educating them correctly. Using E'' isnt right. The correct way to escape a quote is to double quote it: 'Mr. M''vey'

-Andy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

The correct way to escape a quote is to double quote it: 'Mr. M''vey'

That is a matter of opinion. However, the real problem is the enclosed
backslashes, which is
beyond our control at this point. Therefore, the best solution is to use
ESCAPE E.

On Sat, Aug 22, 2015 at 3:49 PM, Andy Colson <andy@squeakycode.net> wrote:

On Sat, Aug 22, 2015 at 1:16 PM, Tom Lane <tgl@sss.pgh.pa.us <mailto:

tgl@sss.pgh.pa.us>> wrote:

Melvin Davidson <melvin6925@gmail.com <mailto:melvin6925@gmail.com>>
writes:

Best Practice would rather be something along the lines:

Avoid coding in a way that triggers "WARNING:
nonstandard use of escape in a string literal". If you
cannot comply with this rule document your reasons.

Thanks for the suggestion. For the past few months I've been

dealing with

an error log that is filled with these warnings simply because
the developers do not comprehend how to use ( or the requirement to

use)

an escape clause.

IMO best practice in this area is "run with
standard_conforming_strings = ON".
If you're seeing this warning at all, it's because you aren't doing
that,
which means your code is unnecessarily unportable to other DBMSes.
Adopting a coding policy of always using E'' would make that worse.

regards, tom lane

On 08/22/2015 02:40 PM, Melvin Davidson wrote:

Tom,

Thank you for pointing out "run with standard_conforming_strings = ON"..
However, that is NOT the problem.
What is occurring is that the developers are sending strings like 'Mr.
M\'vey',
which, if we set standard_conforming_strings = ON, would, and does,
result in errors and the statement failing,
which is a lot less desirable that a simple warning.

Therefore, I am trying to educate the developers in the proper method of
escaping strings,
instead of loading up the error log with annoying warnings.

Please dont top post.

But you are not educating them correctly. Using E'' isnt right. The
correct way to escape a quote is to double quote it: 'Mr. M''vey'

-Andy

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Saturday, August 22, 2015, Melvin Davidson <melvin6925@gmail.com> wrote:

The correct way to escape a quote is to double quote it: 'Mr. M''vey'

That is a matter of opinion. However, the real problem is the enclosed
backslashes, which is
beyond our control at this point. Therefore, the best solution is to use
ESCAPE E.

Why is this a best practice and not just "how things work"? If you want to
use backlash escapes you use E''. If you don't use a backslash escape it
doesn't matter - except if you are writing a backslash and don't want to
have to escape it.

Beyond that turn your idea of best practice into a requirement and enable
standard-conforming-strings.

Always using E'' is pragmatic advice but hardly worthy of being considered
best practice. The best practice is to write code in such a way that you
can leave standard conforming strings off AND not generate any warnings.

David J.

On 8/22/2015 12:40 PM, Melvin Davidson wrote:

What is occurring is that the developers are sending strings like 'Mr.
M\'vey',
which, if we set standard_conforming_strings = ON, would, and does,
result in errors and the statement failing,
which is a lot less desirable that a simple warning.

if your developers were writing invalid C code (or whatever other
language they program in), would you modify the compiler to accept their
invalid syntax? or would you tell the developer to fix their code
properly ? if the developers refused, why would you not fire them on
the spot for incompetence ?

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

John,

I believe you and I think alike. The truth is, I was brought on as a
consultant to help this client, so I do not have the authority to fire the
developers. Rather, I am trying to help them fix the absolute worst
designed DB and coding I have seen in 15 years of working with PostgreSQL.
So I've asked for input on additional guidelines to help try to help them
understand the right way to do things.

Unfortunately, so far, people seem to fixate on item one of my guidelines
and I've had no additional suggestions.

On Sat, Aug 22, 2015 at 4:37 PM, John R Pierce <pierce@hogranch.com> wrote:

On 8/22/2015 12:40 PM, Melvin Davidson wrote:

What is occurring is that the developers are sending strings like 'Mr.
M\'vey',
which, if we set standard_conforming_strings = ON, would, and does,
result in errors and the statement failing,
which is a lot less desirable that a simple warning.

if your developers were writing invalid C code (or whatever other language
they program in), would you modify the compiler to accept their invalid
syntax? or would you tell the developer to fix their code properly ? if
the developers refused, why would you not fire them on the spot for
incompetence ?

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Sat, Aug 22, 2015 at 7:33 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:

John,

I believe you and I think alike. The truth is, I was brought on as a
consultant to help this client, so I do not have the authority to fire the
developers. Rather, I am trying to help them fix the absolute worst
designed DB and coding I have seen in 15 years of working with PostgreSQL.
So I've asked for input on additional guidelines to help try to help them
understand the right way to do things.

Unfortunately, so far, people seem to fixate on item one of my guidelines
and I've had no additional suggestions.

​​So is this a style guide for this one client or a best-practices guide?
From the subsequent elaboration I'll assume it is a style guide for a
client...

1. OK, better they simply do this going forward than break existing
warnings-only stuff by enabling standard conforming strings.
2. I do not get how the advice reconciles with the comment. Besides,
presumes an unstated client application to actually execute those
statements. Again, this is why it is a style guide for one client and not
a general purpose best practices.
3. Yes - though to be honest this only matters at the top-level of the
query. Sub-queries can make use of "SELECT *" without the performance
downside (I think) and so there boils down to communication with the reader.
4. Good general advice but a handful of simplistic examples seems like
inadequate training.
5. I would include how and when to use underscores.
6. My argument here would be that since in some cases you must use the
constraint syntax (e.g., multi-column PK) it should be used in all cases
for consistency. If you are going to recommend they provide their own name
the form of that name should be specified. But the default is usually
adequate so that seems like a very minor point to bring up and divert
concentration and memory too.
7. (going from memory) Why hasn't PostgreSQL adopted the MySQL syntax of
allowing "COMMENT ..." directly within CREATE DDL? Especially for column
comments.
8. Agreed; I find this potentially opens a "do you prefix all table
columns" discussion and have seen arguments to the effect of "but my ORM
expects the 'id' convention'.
9. I agree with the sentiment but the example and extent of explanation
seems lacking IMO...
10. Style.

Mechanics: 1, 3, 5, 6
Usability: 4, 7, 8
Style: 2,10
Modelling: 9

Based upon your definition of DBA only the following are in-scope:
1 - applications works but logs are full of warnings
3 - application consumes more resources than needed
7 - helps the DBA understand what data is in the database
10 - toss the DBA a bone by having all of their indexes have a consistent
form.

If you want to expand the DBA role to application support and maintenance
some of the other items would possibly come into scope.

In so far as a poorly defined model can load the database #9 is
important...but I would not mix PostgreSQL style and usage suggestions with
data modelling education.

David J.

Melvin Davidson schrieb am 22.08.2015 um 21:40:

Thank you for pointing out "run with standard_conforming_strings = ON"..
However, that is NOT the problem.
What is occurring is that the developers are sending strings like 'Mr. M\'vey',
which, if we set standard_conforming_strings = ON, would, and does, result in errors and the statement failing,
which is a lot less desirable that a simple warning.

Therefore, I am trying to educate the developers in the proper method of escaping strings,
instead of loading up the error log with annoying warnings.

I strongly disagree: the error is not "annoying" and the statement _should_ fail.

The only way you can make the developers stop using that non-standard syntax is to make the satement fail.

Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Melvin Davidson schrieb am 22.08.2015 um 17:15:

I've attached a file with a few starters that although are numbered,
are in no special order.

2. End ALL queries with a semi-colon (;)
EG: SELECT some_column FROM a_table;

Although autocommit is on by default, it is always a good idea to signal the query processor that a statement is complete with the semicolon.
Failure to do so could result in <IDLE IN TRANSACTION>, which will
hold locks on the tables involved and prevent other queries from being processed.

Terminating a statement with ; has nothing to do with "<idle in transaction>" connections.
It is a mere syntax thing to make the SQL client (e.g. psql) recognize the end of the statement.
If you don't use it, your statement won't be executed in the first place - at least with psql
as it will wait indefinitely until you finish typing the statement. A GUI client might simply send
the wrong statement to the backend.

If you run with autocommit disabled, ending each statement with a semicolon, will not prevent your connection
from getting into that "<idle in transaction>" state. You have to end the _transaction_ using commit or
rollback to avoid that.

I do agree with the "end all queries with a semi-colon" rule, but the explanation is wrong.

You should have another rule that says:

End all transactions as soon as possible using commit or rollback.

Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

David,

Thank you. This is exactly the type of feedback I was looking for. To
answer your question, for now it is a guide for one particular client,
however, if I get enough feedback and contributions, I will revise it and
submit to the PostgreSQL community.

On Mon, Aug 24, 2015 at 2:34 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:

Melvin Davidson schrieb am 22.08.2015 um 17:15:

I've attached a file with a few starters that although are numbered,
are in no special order.

2. End ALL queries with a semi-colon (;)
EG: SELECT some_column FROM a_table;

Although autocommit is on by default, it is always a good idea to

signal the query processor that a statement is complete with the semicolon.

Failure to do so could result in <IDLE IN TRANSACTION>, which will
hold locks on the tables involved and prevent other queries from

being processed.

Terminating a statement with ; has nothing to do with "<idle in
transaction>" connections.
It is a mere syntax thing to make the SQL client (e.g. psql) recognize the
end of the statement.
If you don't use it, your statement won't be executed in the first place -
at least with psql
as it will wait indefinitely until you finish typing the statement. A GUI
client might simply send
the wrong statement to the backend.

If you run with autocommit disabled, ending each statement with a
semicolon, will not prevent your connection
from getting into that "<idle in transaction>" state. You have to end the
_transaction_ using commit or
rollback to avoid that.

I do agree with the "end all queries with a semi-colon" rule, but the
explanation is wrong.

You should have another rule that says:

End all transactions as soon as possible using commit or rollback.

Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert <Karsten.Hilbert@gmx.net>
wrote:

1. Prefix ALL literals with an Escape
EG: SELECT E'This is a \'quoted literal \'';
SELECT E'This is an unquoted literal';

Doing so will prevent the annoying "WARNING: nonstandard use of

escape in a string literal"

I'd be concerned that what is missing here is the bigger issue of Best
Practice #0: Use Bound Variables.
The only way I've seen invalid literals show up in SQL queries is through
the dynamic generation of SQL Statements vs. using bound variables.
Not using bound variables is your doorway to SQL injection exploits.

9. Do NOT arbitrarily assign an "id" column to a table as a primary key

when other columns
are perfectly suited as a unique primary key.

...

Good example:

CREATE TABLE accounts
( accout_id bigint NOT NULL ,

I would not consider the general use of natural primary keys to be best
practice.
Let's assume your account_id field is used as a foreign key in a dozen
other tables.
1) What happens if someone mis-types the account-id?
To correct that, you also need to correct the FK field in the other
dozen tables.
2) What happens when your company starts a new project (or buys a
competitor) and all the new account numbers are alpha-numeric?
3) Your example shows the id as a bigint, but your rule is not limited to
integers.
What if your table is country populations and the primary key is country
name?
Now, you have quite large foreign keys (and a country changing its name is
not unheard of).
(and let's not even get started on case-sensitivity or character encodings).

--
Raymond Cote, President
voice: +1.603.924.6079 email: rgacote@AppropriateSolutions.com skype:
ray.cote

9.

1) What happens if someone mis-types the account-id?
To correct that, you also need to correct the FK field in the other

dozen tables.

2) What happens when your company starts a new project (or buys a

competitor) >and all the new account numbers are alpha-numeric?

I would reply that in good applications, the user DOES NOT type the key,
but rather selects from a drop down list, or the app looks it up / enters
it for them. Besides, it's just as easy to miskey an integer as it is an
aplha numeric. The point is, do not create two primary pkey's when one will
do.

On Mon, Aug 24, 2015 at 9:15 AM, Ray Cote <rgacote@appropriatesolutions.com>
wrote:

On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert <Karsten.Hilbert@gmx.net

wrote:

1. Prefix ALL literals with an Escape
EG: SELECT E'This is a \'quoted literal \'';
SELECT E'This is an unquoted literal';

Doing so will prevent the annoying "WARNING: nonstandard use of

escape in a string literal"

I'd be concerned that what is missing here is the bigger issue of Best
Practice #0: Use Bound Variables.
The only way I've seen invalid literals show up in SQL queries is through
the dynamic generation of SQL Statements vs. using bound variables.
Not using bound variables is your doorway to SQL injection exploits.

9. Do NOT arbitrarily assign an "id" column to a table as a primary key

when other columns
are perfectly suited as a unique primary key.

...

Good example:

CREATE TABLE accounts
( accout_id bigint NOT NULL ,

I would not consider the general use of natural primary keys to be best
practice.
Let's assume your account_id field is used as a foreign key in a dozen
other tables.
1) What happens if someone mis-types the account-id?
To correct that, you also need to correct the FK field in the other
dozen tables.
2) What happens when your company starts a new project (or buys a
competitor) and all the new account numbers are alpha-numeric?
3) Your example shows the id as a bigint, but your rule is not limited to
integers.
What if your table is country populations and the primary key is country
name?
Now, you have quite large foreign keys (and a country changing its name is
not unheard of).
(and let's not even get started on case-sensitivity or character
encodings).

--
Raymond Cote, President
voice: +1.603.924.6079 email: rgacote@AppropriateSolutions.com skype:
ray.cote

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Mon, Aug 24, 2015 at 9:27 AM, Melvin Davidson <melvin6925@gmail.com>
wrote:

9.

1) What happens if someone mis-types the account-id?
To correct that, you also need to correct the FK field in the other

dozen tables.

2) What happens when your company starts a new project (or buys a

competitor) >and all the new account numbers are alpha-numeric?

I would reply that in good applications, the user DOES NOT type the key,
but rather selects from a drop down list, or the app looks it up / enters
it for them. Besides, it's just as easy to miskey an integer as it is an
aplha numeric. The point is, do not create two primary pkey's when one will
do.

​Your missing the point. The existing "Account ID" that you refer to is
apparently externally defined. Pretend it is a social security number.
How would one create a new user in your system, and record their
account_id/social-security-number, without typing it in. What then if it
is discovered that the keyed in value was mis-typed?

​The "point" is to not introduce redundant information. Creating your own
surrogate identifier in order to avoid using a surrogate identifier value
created by another system does not introduce redundancy but rather provides
the system using the primary key control over its generation and, more
importantly, format. The highly situational nature of this is why "data
modelling" is not something I'd incorporate in a "usage" document.​

David J.
​

On Mon, Aug 24, 2015 at 9:15 AM, Ray Cote <rgacote@appropriatesolutions.com>
wrote:

On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert <Karsten.Hilbert@gmx.net

wrote:

1. Prefix ALL literals with an Escape
EG: SELECT E'This is a \'quoted literal \'';
SELECT E'This is an unquoted literal';

Doing so will prevent the annoying "WARNING: nonstandard use of

escape in a string literal"

I'd be concerned that what is missing here is the bigger issue of Best
Practice #0: Use Bound Variables.
The only way I've seen invalid literals show up in SQL queries is through
the dynamic generation of SQL Statements vs. using bound variables.
Not using bound variables is your doorway to SQL injection exploits.

​SELECT * FROM joblist WHERE job_status = 'Active'; is not at risk of an
exploit...but your point is still a good one.

The other area where this is likely to crop up is in using regular
expressions. From that use case alone I've learned to only use E'' when I
need the escaping behavior of the blackslash. Since you rare need that
when constructing a regexp I would rare write a regexp literal using E''.

David J.

What then if it is discovered that the keyed in value was mis-typed?

That is why SQL has UPDATE and DELETE statements. If a primary key is
incorrect,
it can be fixed, be it one method of another.

On Mon, Aug 24, 2015 at 10:04 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Mon, Aug 24, 2015 at 9:27 AM, Melvin Davidson <melvin6925@gmail.com>
wrote:

9.

1) What happens if someone mis-types the account-id?
To correct that, you also need to correct the FK field in the other

dozen tables.

2) What happens when your company starts a new project (or buys a

competitor) >and all the new account numbers are alpha-numeric?

I would reply that in good applications, the user DOES NOT type the key,
but rather selects from a drop down list, or the app looks it up / enters
it for them. Besides, it's just as easy to miskey an integer as it is an
aplha numeric. The point is, do not create two primary pkey's when one will
do.

​Your missing the point. The existing "Account ID" that you refer to is
apparently externally defined. Pretend it is a social security number.
How would one create a new user in your system, and record their
account_id/social-security-number, without typing it in. What then if it
is discovered that the keyed in value was mis-typed?

​The "point" is to not introduce redundant information. Creating your own
surrogate identifier in order to avoid using a surrogate identifier value
created by another system does not introduce redundancy but rather provides
the system using the primary key control over its generation and, more
importantly, format. The highly situational nature of this is why "data
modelling" is not something I'd incorporate in a "usage" document.​

David J.
​

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On Mon, Aug 24, 2015 at 10:32 AM, Melvin Davidson <melvin6925@gmail.com>
wrote:

What then if it is discovered that the keyed in value was mis-typed?

That is why SQL has UPDATE and DELETE statements. If a primary key is
incorrect,
it can be fixed, be it one method of another.

​Yes, a DBA can use ON DELETE CASCADE and ON UPDATE CASCADE​

​to manually resolve the issue of a typo. At scale it is not a clear-cut
solution, however.

David J.