SQLAlchemy Defines New SQL Standard

Dreaming About SQL

Well, I thought that I know the SQL standard a little bit. I also know that there is Limit/Offset Hell in the RDBMS world as each engine implements its own version of limiting records (yea, some brave programmers even implement their own version – getting all records from table and sort them in the application). Today story is a little bit frightening one. I thout that it is just a mistake, but isn’t. Now I don’t know what to think about it.

Primary Key

Relational database standard defines some table constraints that should be implemented in a database schema for data integrity like:

• UNIQUE
• NOT NULL
• PRIMARY KEY

Primary key means both: UNIQUEand NOT NULL. Yea, I also thought so.

Primary key can be defined on one column, but also on many columns. For example defining it like this:

PRIMARY KEY(A, B, C);

means that there can be only one record that has in those columns some set of values (that’s UNIQUE part). But also none of these values can be null (this is the NOT NULL part).

So this should fail in the second and the third statement:

INSERT INTO tablex (A,B,C) VALUES ('10', '11', '12');
INSERT INTO tablex (A,B,C) VALUES ('10', '11', '12');
INSERT INTO tablex (A,B,C) VALUES ('10', '11', NULL);

SqlAlchemy

This is a nice ORM tool (if any ORM can be nice, I doubt). Anyway, this is a nice piece of code… till now. I’ve found a ticket like this one:

query.count() should not count NULL primary keys

the criterion should be improved to not select primary keys that are null. mapper.allow_null_pks should be consulted as well.

http://www.sqlalchemy.org/trac/ticket/889

That led me to the documentation where I found another interesting part:

allow_null_pks

Indicates that composite primary keys where one or more (but not all) columns contain NULL is a valid primary key. Primary keys which contain NULL values usually indicate that a result row does not contain an entity and should be skipped.

http://www.sqlalchemy.org/docs/05/reference/orm/mapping.html

Oh man, that means only one thing: Primary Keys on multiple columns in SqlAlchemy can have null values. GREAT.

But then they are not Primary Keys anymore.

Further Ideas

So maybe let’s take a UNIQUE constraint and allow there repeating values.

Or better: NOT NULL that sometimes can be null.

WHAT A CRAP!!!

Small Update

In the new documentation for the 0.6 version there is something more:

• allow_null_pks – This flag is deprecated – this is stated as allow_partial_pks which defaults to True.
• allow_partial_pks – Defaults to True. Indicates that a composite primary key with some NULL values should be considered as possibly existing within the database. This affects whether a mapper will assign an incoming row to an existing identity, as well as if session.merge() will check the database first for a particular primary key value. A “partial primary key” can occur if one has mapped to an OUTER JOIN, for example.

http://www.sqlalchemy.org/docs/reference/orm/mapping.html?highlight=allow_null_pks#defining-mappings

I have no idea why someone want’s to have a primary key in the ORM that possibly existing within the database. I thought that it would be easier to have a primary key or not. Yes, I fully agree that loading partial object (taken from outer join or somewhere else) can lead to this problem. Just because you create an object without all needed data for the object to be valid. But this way you create some not valid object that shouldn’t exist in a properly written software.

For me this is another argument against ORMs as a totally failed idea, mapping relational data into object world simply doesn’t work.

Bookmark It

Related posts:

1. How to Ensure There is One Row… part 2 Some time ago I wrote about ensuring that there is...
2. Unit Test Your Database. I observe the endless war about TDD. On one side...
3. MySQL SQL Mode Fun While looking for some information on the web, I found...
4. How to Ensure There is One Row With a Value The Problem Let’s imagine a business requirement that there should...
5. The Worst Database Design Let’s create tables… like this: CREATE TABLE A ( ID...
6. Why Use ORMs? Why Use ORMs? ORM aka Object Relational Mapping is a...