Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Aug 1, 2014, at 6:57 AM, Wolfgang Keller felip...@gmx.net wrote: Celko's books are great but surrogate integer PKs are an unavoidable practice within relational databases, they are a requirement of most DBAs I've dealt with Those guys don't even have a faint clue how clueless they are. In fact this very issue is *THE* litmus test question I ask people who claim to be software developers or database admins. as they perform predictably in terms of indexing and space requirements, Surrogate keys will inevitably produce inconsistent data garbage. And data garbage can kill people, among others. I've never had that problem. Won't work. Check the index of any decent database design handbook for overlapping foreign keys. Postgresql at least allows you to create foreign keys that target columns that are only within a unique constraint and not the primary key. SQLAlchemy as of 0.8 can fully handle those wacky overlapping foreign key models, but in practice even the users that actually need those still seem to be dealing with schemas that ultimately use at least partially surrogate PKs, the overlapping FK would be on a PK that consists of something like id, version_id, still not quite a fully natural primary key. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
Celko's books are great but surrogate integer PKs are an unavoidable practice within relational databases, they are a requirement of most DBAs I've dealt with Those guys don't even have a faint clue how clueless they are. In fact this very issue is *THE* litmus test question I ask people who claim to be software developers or database admins. as they perform predictably in terms of indexing and space requirements, Surrogate keys will inevitably produce inconsistent data garbage. And data garbage can kill people, among others. And performance is simply irrelevant when the optimisation result is data garbage. especially considering that a PK implies the format of all the FKs that will refer to it. Typically a UNIQUE constraint is placed on the natural key to prevent dupes. Won't work. Check the index of any decent database design handbook for overlapping foreign keys. Sincerely, Wolfgang -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Wednesday, July 30, 2014 12:18:03 PM UTC-4, Michael Bayer wrote: Typically a UNIQUE constraint is placed on the “natural” key to prevent dupes. Sidenote on this: If the field isn't case-sensitive, then you are often better off using a function on the constraint if the database allows it. Works perfect on things like email addresses under Postgres: CREATE UNIQUE INDEX ON email_address(lower(address)); -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
Hi Rich,
I don't like using 'name' columns as primary keys I would instead use an
'id' column and would set 'index=True' on the name column.
On the primary key also define a Sequence:
Column('id', Integer, Sequence('tablename_id_seq'), primary_key=True)
http://sqlalchemy.readthedocs.org/en/rel_0_9/dialects/postgresql.html?highlight=sequence
For name columns I would use Unicode instead of string.
You might want to read the following.
http://sqlalchemy.readthedocs.org/en/rel_0_9/core/metadata.html#sqlalchemy.schema.MetaData.params.naming_convention
https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/NamingConventions
Werner
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Wed, 30 Jul 2014, Werner wrote: I don't like using 'name' columns as primary keys I would instead use an 'id' column and would set 'index=True' on the name column. Werner, The use of natural keys (such as a vehicle VIN, the US's SSN, or equipment serial number) is prefered over an artificial, meaningless, integer key to prevent duplicate data. See any of Joe Celko's SQL books. Over the past 30 or so years I've resorted to artificial keys only when absolutely necessary. Consider a table for water chemistry constituent concentrations. There can be no more than one row for the concentration of a specified constituent from a distinct location on a given day. The only way to ensure this uniqueness is with the compound primary key of (parameter, sampdate, site). An articial 'id' column fails to prevent duplication because someone could enter the same laboratory results more than once and each row would have a unique 'id' primary key but duplicate data. In the early 1990s I was fired from a database consulting assignment with a medical resarch unit because I changed their flat-file database structure to a relational schema and turned up duplcate data for a number of patients. When you consider the effects on published analyses of data that contained duplicate entries, they had to pick a scapegoat and I was it. :-) Seriously, read Joe Celko's SQL for Smarties (I think the 4th edition is the latest) for robust DDL practices. The unicode vs string suggestion is interesting. I'm not sure of the advantages (or disadvantages) but if the change is neutral I'll run a global search-and-replace. Thanks, Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
Hi Rich, On 7/30/2014 15:04, Rich Shepard wrote: On Wed, 30 Jul 2014, Werner wrote: I don't like using 'name' columns as primary keys I would instead use an 'id' column and would set 'index=True' on the name column. Werner, The use of natural keys (such as a vehicle VIN, the US's SSN, or equipment serial number) is prefered over an artificial, meaningless, integer key to prevent duplicate data. See any of Joe Celko's SQL books. His book looks very interesting. Over the past 30 or so years I've resorted to artificial keys only when absolutely necessary. Consider a table for water chemistry constituent concentrations. There can be no more than one row for the concentration of a specified constituent from a distinct location on a given day. The only way to ensure this uniqueness is with the compound primary key of (parameter, sampdate, site). An articial 'id' column fails to prevent duplication because someone could enter the same laboratory results more than once and each row would have a unique 'id' primary key but duplicate data. I can see the advantage these things, but not sure on 'agency_contacts'. In the early 1990s I was fired from a database consulting assignment with a medical resarch unit because I changed their flat-file database structure to a relational schema and turned up duplcate data for a number of patients. When you consider the effects on published analyses of data that contained duplicate entries, they had to pick a scapegoat and I was it. :-) Seriously, read Joe Celko's SQL for Smarties (I think the 4th edition is the latest) for robust DDL practices. The unicode vs string suggestion is interesting. I'm not sure of the advantages (or disadvantages) but if the change is neutral I'll run a global search-and-replace. I find it easier to deal with things like 'éüö' etc and IIUC there is no en/decoding if you use Unicode in the DB and Python. Werner -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Jul 30, 2014, at 9:04 AM, Rich Shepard rshep...@appl-ecosys.com wrote: On Wed, 30 Jul 2014, Werner wrote: I don't like using 'name' columns as primary keys I would instead use an 'id' column and would set 'index=True' on the name column. Werner, The use of natural keys (such as a vehicle VIN, the US's SSN, or equipment serial number) is prefered over an artificial, meaningless, integer key to prevent duplicate data. See any of Joe Celko's SQL books. Celko's books are great but surrogate integer PKs are an unavoidable practice within relational databases, they are a requirement of most DBAs I've dealt with as they perform predictably in terms of indexing and space requirements, especially considering that a PK implies the format of all the FKs that will refer to it. Typically a UNIQUE constraint is placed on the natural key to prevent dupes. In my own experience we actually tried using meaningful UUIDs as primary keys in a project some years ago and it was an utter disaster. All PK / FK indexes quadrupled in space and performance suffered terribly. This was on a Postgresql backend which should have been a better performer in a non-standard context like that (on a big ol' DB like SQL server, forget it). -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Wed, 30 Jul 2014, Michael Bayer wrote: Celko's books are great but surrogate integer PKs are an unavoidable practice within relational databases, they are a requirement of most DBAs I've dealt with as they perform predictably in terms of indexing and space requirements, especially considering that a PK implies the format of all the FKs that will refer to it. Typically a UNIQUE constraint is placed on the natural key to prevent dupes. Mike, That's interesting. I've not had any issues, but I've not developed many multi-user, large databases. In my own experience we actually tried using meaningful UUIDs as primary keys in a project some years ago and it was an utter disaster. All PK / FK indexes quadrupled in space and performance suffered terribly. This was on a Postgresql backend which should have been a better performer in a non-standard context like that (on a big ol' DB like SQL server, forget it). Wonder if that's been improved in later versions. So, do you recommend that surrogate keys be used in all tables, or only on those that meet certain criteria? I'm always open to learning new things and improving the work I do. Regards, Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Jul 30, 2014, at 12:28 PM, Rich Shepard rshep...@appl-ecosys.com wrote: On Wed, 30 Jul 2014, Michael Bayer wrote: Celko's books are great but surrogate integer PKs are an unavoidable practice within relational databases, they are a requirement of most DBAs I've dealt with as they perform predictably in terms of indexing and space requirements, especially considering that a PK implies the format of all the FKs that will refer to it. Typically a UNIQUE constraint is placed on the natural key to prevent dupes. Mike, That's interesting. I've not had any issues, but I've not developed many multi-user, large databases. In my own experience we actually tried using meaningful UUIDs as primary keys in a project some years ago and it was an utter disaster. All PK / FK indexes quadrupled in space and performance suffered terribly. This was on a Postgresql backend which should have been a better performer in a non-standard context like that (on a big ol' DB like SQL server, forget it). Wonder if that's been improved in later versions. So, do you recommend that surrogate keys be used in all tables, or only on those that meet certain criteria? I'm always open to learning new things and improving the work I do. I find surrogate PKs very easy to deal with in that I never have to worry about UPDATE CASCADE situations, some of which are thorny enough that even SQLAlchemy doesn't have a 100% approach for every situation. With that, plus the predictable indexing, I'm always going to use them. But, I think there's a fair degree of preference still here. With natural PKs, the biggest issue is how much space indexes are going to take up considering that everything that FKs to that PK has to mirror out those same columns. Just on those occasions I've had to deal with the Trac database, which uses natural primary keys, for every table it's a mystery how to join things, b.c. every table has a different notion of primary. But then again they also have pretty inconsistent conventions. I think natural PKs just complicate the natural plumbing of being able to wire rows together, they are of course more pure but in relational DBs we are already seriously impure compared to Codd, RDBMS draws from relational algebra to the point that is practical and that's about it. You of course will always have a natural key and hopefully adequate constraints on it. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Wed, 30 Jul 2014, Michael Bayer wrote: With that, plus the predictable indexing, I'm always going to use them. But, I think there's a fair degree of preference still here. With natural PKs, the biggest issue is how much space indexes are going to take up considering that everything that FKs to that PK has to mirror out those same columns. Mike, Fair enough. In the current application I'm developing most tables will have comparatively few rows ( 500 in most cases). The data tables can easily have 100,000 rows, but the only table that would relate to those is the 'changed' table which records what, when, why, and by whom a change was made. This is for audit trail purposes. Thanks for your insights, Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Wed, 30 Jul 2014, Michael Bayer wrote: Typically a UNIQUE constraint is placed on the natural key to prevent dupes. I can see this when the natural key is a single column, but wonder how a compound natural key is represented if a serial integer is used as the surrogate 'id' key. For example, class Changed_Data(Base): __tablename__ = changed_data id = Column(Integer, primary_key = True) which_table = Column(unicode(32), nullable = False) which_attrib = Column(unicode(32), nullable = False) when_changed = Column(Timestamp, nullable = False) curr_value = Column(Unicode(32), nullable = False) new_value = Column(Unicode(32), nullable = False) changed_by = Column(Unicode(32), nullable = False) reason = Column(Text) The postgres schema specifies the primary key as (which_table, which_attribute, when_changed). If I make each of those columns Unique is it the set of columns that is unique or each individual column? TIA, Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
You make a single explicit UniqueConstraint object that specifies all three. Sent from my iPhone On Jul 30, 2014, at 2:30 PM, Rich Shepard rshep...@appl-ecosys.com wrote: On Wed, 30 Jul 2014, Michael Bayer wrote: Typically a UNIQUE constraint is placed on the natural key to prevent dupes. I can see this when the natural key is a single column, but wonder how a compound natural key is represented if a serial integer is used as the surrogate 'id' key. For example, class Changed_Data(Base): __tablename__ = changed_data id = Column(Integer, primary_key = True) which_table = Column(unicode(32), nullable = False) which_attrib = Column(unicode(32), nullable = False) when_changed = Column(Timestamp, nullable = False) curr_value = Column(Unicode(32), nullable = False) new_value = Column(Unicode(32), nullable = False) changed_by = Column(Unicode(32), nullable = False) reason = Column(Text) The postgres schema specifies the primary key as (which_table, which_attribute, when_changed). If I make each of those columns Unique is it the set of columns that is unique or each individual column? TIA, Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Wed, 30 Jul 2014, Michael Bayer wrote: You make a single explicit UniqueConstraint object that specifies all three. Thanks, Mike. I missed that in the docs. Next question will appear only after a thorough search of your book. Rich -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
[sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
I'm starting to learn SQLAlchemy; have 0.9.7 installed on Slackware-14.1
with Python-2.7.5, wxPython-3.0.0.0, and postgresql-9.3.4.
1) In the docs I see a row constraint example using an integer comparison
attrib.CheckConstraint('attrib5'). What is the syntax for a list, such as
this postgres example?
CHECK (agency_name IN ('Federal', 'State', 'County', 'City', 'Local',
'Regional'),
Do I write agency_name.CheckConstraint('Federal', 'State', 'County',
'City', 'Local', 'Regional')?
2) I have searched the docs for instructions on how to specify a
multi-column primary key, but have not found an example. The only references
I find to multi-column keys are for foreign keys. My applicaton has several
tables that require multi-column keys.
Rich
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys [RESOLVED]
On Tue, 29 Jul 2014, Simon King wrote:
Sorry, I don't know the answer to this, but based on the attrib example,
I would guess that the string is passed directly to the database, so you
would write something like:
agency_name.CheckConstraint(agency_name IN ('Federal', 'State',
'County', 'City', 'Local', 'Regional'))
Simon,
I saw a single column and value for the example, but did not extrapolate
it the way you do. That should do the trick.
Specify the primary_key=True keyword argument for each column that you
want to form part of the primary key.
Ah-ha! I did not pick that up from reading the docs.
Hope that helps,
Certainly does!
Thanks very much,
Rich
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Tue, 29 Jul 2014, Simon King wrote:
Hope that helps,
Simon, and others:
As a check that I understand the basics please check the syntax of this
set of three related tables:
class Agencies(Base):
__tablename__ = 'agencies'
org_name = Column(String(48), primary_key = True)
acronym = Column(String(8), value=' ', nullable = False)
org_lvl = Column(String(8), value='State', nullable = False,
CheckConstraint(org_lvl(org_lvl IN ('Federal', 'State', 'County',
'City', 'Local', 'Regional'))
website = Column(String(64), value=' ')
comment = Column(STring)
class Agency_Units(Base):
__tablename__ = 'agency_units'
unit_name = Column(String(48), nullable = False, unique = True, primary_key
= True)
parent_name = Column(String(48), nullable = False,
ForeignKey('agencies.org_name'), primary_key = True)
agencies = relationship(Agencies, backref=backref('agency_units'))
acronym = Column(String(8))
addr1 = Column(String(32), nullable = False)
addr2 = Column(String(32))
city = Column(String(16), nullable = False)
state_prov = Column(String(2), nullable = False)
postcode = Column(String(10), nullable = False)
phone = Column(String(10))
fax = Column(String(10))
website = Column(String(64))
comment = Column(String)
class Agency_Contacts(Base):
__tablename__ = 'agency_contacts'
last_name = Column(String(20), nullable = False, primary_key = True)
first_name = Column(String(16), nullable = False, primary_key = True)
mi = Column(String(1))
agency_unit = Column(String(48), nullable = False, primary_key = True,
ForeignKey('agency_units.unit_name'))
agency_unites = relationship(Agency_Units,
backref=backref('agency_contacts'))
title = Column(String(32))
phone = Column(String(10), nullable = False)
extension = Column(String(6))
email = Column(String(32))
start_date = Column(Date, nullable = False)
end_date = Column(Date)
comments = Column(String)
I think that's how to express multiple column primary keys and the foreign
references; at least, that's how I interpreted the doc. Getting corrected
now will make life easier in the future.
Thanks in advance,
Rich
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] Specifying a Row Check Constraint and Compound Primary Keys
On Tue, 29 Jul 2014, Rich Shepard wrote:
CheckConstraint(org_lvl(org_lvl IN ('Federal', 'State', 'County',
'City', 'Local', 'Regional'))
Oops! that first 'org_lvl(' comes out.
Rich
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
