Re: Enforce primary key on every table during dev?

On 02/03/2018 01:10 , Daevor The Devoted wrote: On Fri, Mar 2, 2018 at 12:05 AM, Gavin Flower > wrote: On 02/03/18 06:47, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar

Re: Enforce primary key on every table during dev?

On Fri, Mar 2, 2018 at 12:05 AM, Gavin Flower wrote: > On 02/03/18 06:47, Daevor The Devoted wrote: > >> >> On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar > > wrote: >> >> >> >Adding a surrogate key to such

Re: Enforce primary key on every table during dev?

On 01/03/2018 19:05 , Gavin Flower wrote: On 02/03/18 06:47, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar > wrote:     >Adding a surrogate key to such a table just adds overhead,     although that could be

Re: Enforce primary key on every table during dev?

On 01/03/2018 18:41 , Adrian Klaver wrote: On 03/01/2018 01:26 PM, Ron Johnson wrote: On 03/01/2018 03:14 PM, Adrian Klaver wrote: On 03/01/2018 01:03 PM, Ron Johnson wrote: On 03/01/2018 02:32 PM, David G. Johnston wrote: There's always the "account number", which is usually synthetic.

Re: Enforce primary key on every table during dev?

On 02/03/18 06:47, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar > wrote: >Adding a surrogate key to such a table just adds overhead, although that could be useful >in case specific rows need

Re: Enforce primary key on every table during dev?

On 03/01/2018 01:26 PM, Ron Johnson wrote: On 03/01/2018 03:14 PM, Adrian Klaver wrote: On 03/01/2018 01:03 PM, Ron Johnson wrote: On 03/01/2018 02:32 PM, David G. Johnston wrote: There's always the "account number", which is usually synthetic. Credit Card numbers are also synthetic.

Re: Enforce primary key on every table during dev?

On 03/01/2018 03:14 PM, Adrian Klaver wrote: On 03/01/2018 01:03 PM, Ron Johnson wrote: On 03/01/2018 02:32 PM, David G. Johnston wrote: There's always the "account number", which is usually synthetic. Credit Card numbers are also synthetic. Actually, no:

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 2:06 PM, Tim Cross wrote: > +1. And a good test of your underlying data model is whether you can > identify a natural primary key. If you can't, chances are your model is > immature/flawed and needs more analysis. >

Re: Enforce primary key on every table during dev?

On 03/01/2018 01:03 PM, Ron Johnson wrote: On 03/01/2018 02:32 PM, David G. Johnston wrote: There's always the "account number", which is usually synthetic. Credit Card numbers are also synthetic. Actually, no: https://en.wikipedia.org/wiki/Payment_card_number There is a method to the

Re: Enforce primary key on every table during dev?

Ron Johnson writes: > On 03/01/2018 02:08 PM, marcelo wrote: >> >> >> On 01/03/2018 16:42 , Ron Johnson wrote: >>> On 03/01/2018 01:11 PM, marcelo wrote: On 01/03/2018 16:00 , Ron Johnson wrote: >>> [snip] > If your only unique index is a synthetic key, then

Re: Enforce primary key on every table during dev?

On 03/01/2018 02:32 PM, David G. Johnston wrote: On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson >wrote: Why have the overhead of a second unique index?  If it's "ease of joins", then I agree with Francisco Olarte and use the business

Re: Enforce primary key on every table during dev?

I found this thread very interesting. A pivot table is a perfectly valid use case where a compound unique key on two or more columns performs the same function as a primary key without one. I’m not nearly as familiar with Postgres as I am with MySQL (which is why I recently joined this

Re: Enforce primary key on every table during dev?

On 03/01/2018 02:44 PM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 10:32 PM, David G. Johnston > wrote: On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson >wrote:

Re: Enforce primary key on every table during dev?

On 03/01/2018 02:32 PM, marcelo wrote: On 01/03/2018 17:21 , Ron Johnson wrote: On 03/01/2018 02:08 PM, marcelo wrote: On 01/03/2018 16:42 , Ron Johnson wrote: On 03/01/2018 01:11 PM, marcelo wrote: On 01/03/2018 16:00 , Ron Johnson wrote: [snip] If your only unique index is a

Re: Enforce primary key on every table during dev?

On 01/03/2018 17:32 , David G. Johnston wrote: On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson >wrote: Why have the overhead of a second unique index?  If it's "ease of joins", then I agree with Francisco Olarte and use the business

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 10:36 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Mar 1, 2018 at 1:32 PM, marcelo wrote: > >> What´s the question? >> >> > ​Whether the OP, who hasn't come back, knew they were starting a flame war > by asking this

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 10:32 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson wrote: > >> Why have the overhead of a second unique index? If it's "ease of joins", >> then I agree with Francisco Olarte and use the

Re: Enforce primary key on every table during dev?

> On Mar 1, 2018, at 12:47 , Daevor The Devoted wrote: > > > I was always of the opinion that a mandatory surrogate key (as you describe) > is good practice. > Sure there may be a unique key according to business logic (which may be > consist of those > "ungainly"

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 1:32 PM, marcelo wrote: > What´s the question? > > ​Whether the OP, who hasn't come back, knew they were starting a flame war by asking this question... There is no context-less "right place" to place validation logic, nor are the various

Re: Enforce primary key on every table during dev?

On 01/03/2018 17:21 , Ron Johnson wrote: On 03/01/2018 02:08 PM, marcelo wrote: On 01/03/2018 16:42 , Ron Johnson wrote: On 03/01/2018 01:11 PM, marcelo wrote: On 01/03/2018 16:00 , Ron Johnson wrote: [snip] If your only unique index is a synthetic key, then you can insert the same

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson wrote: > Why have the overhead of a second unique index? If it's "ease of joins", > then I agree with Francisco Olarte and use the business logic keys in your > joins even though it's a bit of extra work. > ​The strongest case,

Re: Enforce primary key on every table during dev?

On 03/01/2018 02:09 PM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 9:00 PM, Ron Johnson > wrote: On 03/01/2018 12:32 PM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 1:06 PM, Daevor The Devoted wrote: > >> This seems like hierarchical data > ​Hence the "this is contrived" disclaimer - but if one allows for employee-department to be many-to-many, and thus requiring a joining table, this still applies even if the

Re: Enforce primary key on every table during dev?

On 01/03/2018 16:42 , Ron Johnson wrote: On 03/01/2018 01:11 PM, marcelo wrote: On 01/03/2018 16:00 , Ron Johnson wrote: [snip] If your only unique index is a synthetic key, then you can insert the same "business data" multiple times with different synthetic keys. -- Angular momentum

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 8:52 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Thu, Mar 1, 2018 at 11:32 AM, Daevor The Devoted > wrote: > >> Could you perhaps elaborate on how a surrogate key allows one to insert >> garbage into the table? I'm afraid I don't

Re: Enforce primary key on every table during dev?

On 03/01/2018 01:11 PM, marcelo wrote: On 01/03/2018 16:00 , Ron Johnson wrote: [snip] If your only unique index is a synthetic key, then you can insert the same "business data" multiple times with different synthetic keys. -- Angular momentum makes the world go 'round. IMHO, business

Re: Enforce primary key on every table during dev?

On 03/01/2018 01:05 PM, Melvin Davidson wrote: On Thu, Mar 1, 2018 at 2:00 PM, Ron Johnson > wrote: On 03/01/2018 12:32 PM, Daevor The Devoted wrote: [snip] If your only unique index is a synthetic key, then you can insert

Re: Enforce primary key on every table during dev?

On 01/03/2018 16:00 , Ron Johnson wrote: On 03/01/2018 12:32 PM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson > wrote: On 03/01/2018 11:47 AM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 2:07 PM,

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 2:00 PM, Ron Johnson wrote: > On 03/01/2018 12:32 PM, Daevor The Devoted wrote: > > > > On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson wrote: > >> >> On 03/01/2018 11:47 AM, Daevor The Devoted wrote: >> >> >> On Thu, Mar 1, 2018

Re: Enforce primary key on every table during dev?

On 03/01/2018 12:32 PM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson > wrote: On 03/01/2018 11:47 AM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 11:32 AM, Daevor The Devoted wrote: > Could you perhaps elaborate on how a surrogate key allows one to insert > garbage into the table? I'm afraid I don't quite get what you're saying. > ​A bit contrived but it makes the point:​ *Company:* C1 (id c1)

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 5:22 PM, Steven Lembark wrote: > If you can say that "rows containing the same values are not > duplicates" Not a native speaker, but "Rows having the same values" seems to me the definition of duplicate ( ;-), J.K. ) > then you have a database that

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 1:07 PM, Rakesh Kumar wrote: ... > I routinely add surrogate keys like serial col to a table already having a > nice candidate keys > to make it easy to join tables. SQL starts looking ungainly when you have a > 3 col primary > key and need to

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 8:18 PM, Ron Johnson wrote: > On 03/01/2018 11:47 AM, Daevor The Devoted wrote: > > > On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar > wrote: > >> >> >Adding a surrogate key to such a table just adds overhead, although that >>

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 9:20 AM, Alban Hertroys wrote: > Not to mention that not all types of tables necessarily have suitable > candidates for a primary key. They do if they are in 1NF. ( no dupes alllowed ) > An example of such tables is a monetary transaction table that

Re: Enforce primary key on every table during dev?

Melvin: On Thu, Mar 1, 2018 at 1:47 AM, Melvin Davidson wrote: > But this begs the question, > why are "developers" allowed to design database tables? That should be the > job of the DBA! That's the DBA wearing her developer hat. ( I agree with the spirit ) Francisco

Re: Enforce primary key on every table during dev?

On 03/01/2018 11:47 AM, Daevor The Devoted wrote: On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar > wrote: >Adding a surrogate key to such a table just adds overhead, although that could be useful >in case specific rows need

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 2:07 PM, Rakesh Kumar wrote: > > >Adding a surrogate key to such a table just adds overhead, although that > could be useful > >in case specific rows need updating or deleting without also modifying > the other rows with > >that same data -

Re: Enforce primary key on every table during dev?

On 03/01/2018 11:07 AM, Steve Atkins wrote: [snip] "Every table should have a primary key, whether natural or surrogate" is a great guideline, and everyone should follow it until they understand when they shouldn't. Most people think they know, but they don't. -- Angular momentum makes the

Re: Enforce primary key on every table during dev?

(Melvin's mail doesn't quote properly; I've tried to fix it but may have broken something) > On Mar 1, 2018, at 8:50 AM, Melvin Davidson wrote: > > > On Thu, Mar 1, 2018 at 11:43 AM, Alban Hertroys wrote: > > >> On 1 March 2018 at 17:22, Steven

Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 11:43 AM, Alban Hertroys wrote: > On 1 March 2018 at 17:22, Steven Lembark wrote: > > > >> On 03/01/2018 02:20 AM, Alban Hertroys wrote: > >> [snip] > >> > Not to mention that not all types of tables necessarily have > >> >

Re: Enforce primary key on every table during dev?

On 1 March 2018 at 17:22, Steven Lembark wrote: > >> On 03/01/2018 02:20 AM, Alban Hertroys wrote: >> [snip] >> > Not to mention that not all types of tables necessarily have >> > suitable candidates for a primary key. You could add a surrogate >> > key based on a serial

Re: Enforce primary key on every table during dev?

> On 03/01/2018 02:20 AM, Alban Hertroys wrote: > [snip] > > Not to mention that not all types of tables necessarily have > > suitable candidates for a primary key. You could add a surrogate > > key based on a serial type, but in such cases that may not serve > > any purpose other than to have

Re: Enforce primary key on every table during dev?

- Original Message - > From: "Tim Cross" > Sent: Wednesday, February 28, 2018 4:07:43 PM > > Jeremy Finzel writes: > > > We want to enforce a policy, partly just to protect those who might forget, > > for every table in a particular schema to

Re: Enforce primary key on every table during dev?

>Adding a surrogate key to such a table just adds overhead, although that could >be useful >in case specific rows need updating or deleting without also modifying the >other rows with >that same data - normally, only insertions and selections happen on such >tables though, >and updates or

Re: Enforce primary key on every table during dev?

On 03/01/2018 02:20 AM, Alban Hertroys wrote: [snip] Not to mention that not all types of tables necessarily have suitable candidates for a primary key. You could add a surrogate key based on a serial type, but in such cases that may not serve any purpose other than to have some arbitrary

Re: Enforce primary key on every table during dev?

> On 1 Mar 2018, at 1:47, Melvin Davidson wrote: > > I think you would be better off having an automated report which alerts > >you to tables lacking a primary key and deal with that policy through > >other means. > > Perhaps a better solution is to have a meeting with

Re: Enforce primary key on every table during dev?

On Wed, Feb 28, 2018 at 4:07 PM, Tim Cross wrote: > > Jeremy Finzel writes: > > > We want to enforce a policy, partly just to protect those who might > forget, > > for every table in a particular schema to have a primary key. This can't > > be done

Re: Enforce primary key on every table during dev?

Jeremy Finzel writes: > We want to enforce a policy, partly just to protect those who might forget, > for every table in a particular schema to have a primary key. This can't > be done with event triggers as far as I can see, because it is quite > legitimate to do: > >

Re: Enforce primary key on every table during dev?

On Wed, Feb 28, 2018 at 6:34 AM, Jeremy Finzel wrote: > We want to enforce a policy, partly just to protect those who might > forget, for every table in a particular schema to have a primary key. This > can't be done with event triggers as far as I can see, because it is

Re: Enforce primary key on every table during dev?

On Wed, Feb 28, 2018 at 7:57 AM, Adrian Klaver wrote: > On 02/28/2018 05:52 AM, John McKown wrote: > >> On Wed, Feb 28, 2018 at 7:34 AM, Jeremy Finzel > >wrote: >> >> We want to enforce a policy, partly just to protect

Re: Enforce primary key on every table during dev?

On Wed, Feb 28, 2018 at 8:57 AM, Adrian Klaver wrote: > On 02/28/2018 05:52 AM, John McKown wrote: > >> On Wed, Feb 28, 2018 at 7:34 AM, Jeremy Finzel > >wrote: >> >> We want to enforce a policy, partly just to protect

Re: Enforce primary key on every table during dev?

On 02/28/2018 05:52 AM, John McKown wrote: On Wed, Feb 28, 2018 at 7:34 AM, Jeremy Finzel >wrote: We want to enforce a policy, partly just to protect those who might forget, for every table in a particular schema to have a primary key. 

Re: Enforce primary key on every table during dev?

On Wed, Feb 28, 2018 at 7:34 AM, Jeremy Finzel wrote: > We want to enforce a policy, partly just to protect those who might > forget, for every table in a particular schema to have a primary key. This > can't be done with event triggers as far as I can see, because it is