On Sep 15, 4:08 pm, "Michael Bayer" <mike...@zzzcomputing.com> wrote: > Gregg Lind wrote: > > What I think I'm seeing is that an object can be created even without it's > > ForeignKeyConstraint being filled. > > > To run the test code below: > > > $ dropdb test18; createdb test18; python testcode.py > > "on" is not defined: > > ForeignKeyConstraint(['regstring_id', 'regstring','regstring_type'], > [A1String.id, A1String.string, A1String.origin], on), > > when removing "on", the row inserts with "regstring_id" as NULL. PG > appears to accept this so I would assume PG considers a three-column > foreign key with one NULL to be NULL. If I try it with all three columns > not null, then you get the constraint error. > > SQLalchemy itself relies upon the database to enforce constraints. In > this case you should have the "NOT NULL" constraint on the Product > columns. >
To expand on this: Most (all?) databases default to a MATCH SIMPLE policy for foreign key constraints: if any FK column is NULL then the FK constraint is satisfied (regardless of the actual values of the non-null columns). It looks like you want MATCH FULL behavior: if some but not all FK columns are NULL then the FK constraint fails. Assuming you really do need the the FK columns to be nullable, you have to either add MATCH FULL to your DDL (probably have to use DDL() + ALTER TABLE; also requires that your database actually supports MATCH FULL) or add a check constraint that mimics the MATCH FULL behavior, e.g.: (regstring_id IS NULL) = (regstring IS NULL) = (regstring_type IS NULL) > > This builds on > >http://groups.google.com/group/sqlalchemy/browse_thread/thread/eb240f... > > . > > > I understand that the foreign table can't create the referent. (and > > finding > > the best idiom for "use one if it exists or create one") is yet be > > determined. What I truly don't understand is how any instances of > > "Product" > > can be created, since there is a FK constraint that is not fulfulled. > > > 1. Is the foreign key constraint fulfilled? > > 2. Is there a good "create the referent if it doesn't exist, else use it" > > idiom? > > 3. Is the polymorphic table business complicating it? It seems liek the > > compound primary key for A1String is. > > > ---------------- > > from sqlalchemy.ext.declarative import > > declarative_base > > from sqlalchemy import CheckConstraint, ForeignKey, MetaData, > > PrimaryKeyConstraint > > from sqlalchemy import ForeignKeyConstraint > > from sqlalchemy import Table, Column, Integer, Boolean,Unicode,String > > from sqlalchemy.orm import relation, backref > > from sqlalchemy import create_engine > > from sqlalchemy.orm import sessionmaker > > from sqlalchemy.schema import DDL > > > import sys > > ECHO = bool((sys.argv + [False])[1]) > > > ## utilties for connecting the db, printing it, etc. > > def print_schema(T="postgres", Base=None): > > ''' print print_schema will print the schema in use ''' > > from StringIO import StringIO > > buf = StringIO() > > engine = create_engine('%s://' % T, strategy='mock', executor=lambda > > s, > > p='': buf.write(str(s) + p)) > > Base.metadata.create_all(engine) > > return buf.getvalue() > > > def db_setup(connstring='sqlite:///:memory:', echo=False, Base=None): > > engine = create_engine(connstring, echo=echo) > > Session = sessionmaker(bind=engine, autoflush=False, autocommit=False) > > session = Session() > > Base.metadata.bind = engine > > Base.metadata.create_all() > > return session, engine > > > def _class_repr(self): > > ''' print our SA class instances in a nicer way ''' > > # ugly, use sparingly, may have performance hit > > d = [(k,v) for k,v in self.__dict__.iteritems() if k[0] != "_"] > > d = sorted(d, key=lambda x: x[0].lower()) > > return "<%s, %s>" % (self.__class__, d) > > > Base = declarative_base() > > > class Polystring(Base): > > __tablename__ = 'strings' > > id = Column(Integer, nullable=False, primary_key=True) > > string = Column(String, nullable=False, primary_key=True) > > origin = Column(String, nullable=False, primary_key=True) > > __mapper_args__ = {'polymorphic_on': origin} > > > # subtype of string > > class A1String(Polystring): > > __mapper_args__ = {'polymorphic_identity': 'a1'} > > products = relation('Product', order_by="Product.id") > > > class Product(Base): > > __tablename__ = 'product' > > __table_args__ = ( > > ForeignKeyConstraint(['regstring_id', 'regstring', > > 'regstring_type'], [A1String.id, A1String.string > > , A1String.origin], on), > > {} > > ) > > id = Column(Integer,primary_key=True) > > regstring_id = Column(Integer) > > regstring = Column(String) > > regstring_type = Column(String,default="asn") > > > ## test code > > session,eng = db_setup("postgres:///test18", Base=Base, echo=ECHO) > > add = session.add > > q = session.query > > c = session.commit > > r = _class_repr > > > A = Product(id=192832, regstring="some part id") > > print r(A) > > add(A) > > c() # commit > > print map(r,q(Product).all()) > > print "somehow this managed to get in, without making a polystring, which > > it > > should be referencing." > > assert len(q(Polystring).all()) > 0, "So, where is the polystring?" > > ---------- --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
