Primary keys should be a single column. If you introduce more you create what is called a composite key. They are bane of existence for ORM's and pretty much anyone who needs to write SQL join statements.
Rather than doing a JOIN on with a single column to another table that also contains that column, you then would have to include all those composite keys in the other table as well, and you propogate redundant data amongst tables. I'd rather just propogate one facet of data (the single column key) rather than more than one column. But if you like typing and redundant key data amongst multiple tables, composite keys are the way to go 😊
We have one table that has no single primary key. In order for us to issue a delete against that record we have to do a WHERE clause with 27 columns! I am not kidding on this. I have wanted to change it for years over to DDL and slap on a surrogate key at the front of it, but it requires a lot of RPG programs to be recompiled and no one wants to do it.
-----Original Message-----
From: Rob Berendt [mailto:rob@xxxxxxxxx]
Sent: Monday, August 28, 2017 12:32 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: RE: Database design question
Another thing to consider is: There is nothing which states that a primary key has to be a single column. I'm thinking of the JDEdwards example.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at
http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
http://amzn.to/2dEadiD
midrange.com
