[sqlalchemy] Re: Composite Foreign Key with ondelete='CASCADE' does not work on mysql

2011-05-30 Thread virhilo 
You need to use InnoDB engine, so you tables definitions 'll look
like:

sensors = Table('sensors', metadata,
...
mysql_engine='InnoDB'
)


view_opts = Table('view_opts', metadata,
...
mysql_engine='InnoDB'
)


On 30 Maj, 17:38, neurino neur...@gmail.com wrote:
 I have a composite Primary key in a table and a relative Foreign Key
 in another as:

 sensors = Table('sensors', metadata,
     Column('id_cu', Integer, ForeignKey('ctrl_units.id',
 ondelete='CASCADE'),
             primary_key=True, autoincrement=False),
     Column('id_meas', Integer, primary_key=True, autoincrement=False),
     ...
     )

 view_opts = Table('view_opts', metadata,
     Column('id', Integer, primary_key=True),
     Column('id_view', Integer, ForeignKey('views.id',
 ondelete='CASCADE'),
             nullable=False),

     Column('id_cu', Integer, ForeignKey('ctrl_units.id'),
 nullable=False),
     Column('id_meas', Integer, nullable=False),
     ForeignKeyConstraint(('id_cu', 'id_meas'),
                          ('sensors.id_cu', 'sensors.id_meas'),
                          ondelete='CASCADE'),
     ...
     )

 mapped like this:

 orm.mapper(Sensor, sensors,
     
     properties={
         'view_opts': orm.relationship(ViewOpt, backref='sensor',
             cascade='all, delete-orphan', passive_deletes=True,
             single_parent=True)
     })

 Now when I delete a row from sensor relative view_opt rows are not
 removed.

 I can't understand if this depends on DDL, it's a MySQL bug, something
 sqlalchemy related or whatever.

 I know I set `passive_deletes=True`to get MySQL ONDELETE CASCADE take
 care of it more and more quickly.

 Any help appreciated, thanks for your support
 neurino

-- 
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.

[sqlalchemy] Re: Composite Foreign Key with ondelete='CASCADE' does not work on mysql

2011-05-30 Thread neurino 
Sorry if I did not specified, yes it's InnoDB.

So do I HAVE to put `mysql_engine='InnoDB'` in any Table using
ondelete cascade?

Is there a link to docs with some info on it?

Thanks for your support

On May 30, 7:04 pm, virhilo virh...@gmail.com wrote:
 You need to use InnoDB engine, so you tables definitions 'll look
 like:

 sensors = Table('sensors', metadata,
     ...
     mysql_engine='InnoDB'
     )

 view_opts = Table('view_opts', metadata,
     ...
     mysql_engine='InnoDB'
     )

 On 30 Maj, 17:38, neurino neur...@gmail.com wrote:







  I have a composite Primary key in a table and a relative Foreign Key
  in another as:

  sensors = Table('sensors', metadata,
      Column('id_cu', Integer, ForeignKey('ctrl_units.id',
  ondelete='CASCADE'),
              primary_key=True, autoincrement=False),
      Column('id_meas', Integer, primary_key=True, autoincrement=False),
      ...
      )

  view_opts = Table('view_opts', metadata,
      Column('id', Integer, primary_key=True),
      Column('id_view', Integer, ForeignKey('views.id',
  ondelete='CASCADE'),
              nullable=False),

      Column('id_cu', Integer, ForeignKey('ctrl_units.id'),
  nullable=False),
      Column('id_meas', Integer, nullable=False),
      ForeignKeyConstraint(('id_cu', 'id_meas'),
                           ('sensors.id_cu', 'sensors.id_meas'),
                           ondelete='CASCADE'),
      ...
      )

  mapped like this:

  orm.mapper(Sensor, sensors,
      
      properties={
          'view_opts': orm.relationship(ViewOpt, backref='sensor',
              cascade='all, delete-orphan', passive_deletes=True,
              single_parent=True)
      })

  Now when I delete a row from sensor relative view_opt rows are not
  removed.

  I can't understand if this depends on DDL, it's a MySQL bug, something
  sqlalchemy related or whatever.

  I know I set `passive_deletes=True`to get MySQL ONDELETE CASCADE take
  care of it more and more quickly.

  Any help appreciated, thanks for your support
  neurino

-- 
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.

Re: [sqlalchemy] Re: Composite Foreign Key with ondelete='CASCADE' does not work on mysql

2011-05-30 Thread Michael Bayer 
The table has to be created with both InnoDB as well as ON DELETE CASCADE, 
on the MySQL side, meaning these both must be present in the CREATE TABLE 
statement used to create the tables in the database.   On the SQLAlchemy side, 
these options don't have any meaning outside the emission of the CREATE 
statement.


On May 30, 2011, at 6:10 PM, neurino wrote:

 Sorry if I did not specified, yes it's InnoDB.
 
 So do I HAVE to put `mysql_engine='InnoDB'` in any Table using
 ondelete cascade?
 
 Is there a link to docs with some info on it?
 
 Thanks for your support
 
 On May 30, 7:04 pm, virhilo virh...@gmail.com wrote:
 You need to use InnoDB engine, so you tables definitions 'll look
 like:
 
 sensors = Table('sensors', metadata,
 ...
 mysql_engine='InnoDB'
 )
 
 view_opts = Table('view_opts', metadata,
 ...
 mysql_engine='InnoDB'
 )
 
 On 30 Maj, 17:38, neurino neur...@gmail.com wrote:
 
 
 
 
 
 
 
 I have a composite Primary key in a table and a relative Foreign Key
 in another as:
 
 sensors = Table('sensors', metadata,
 Column('id_cu', Integer, ForeignKey('ctrl_units.id',
 ondelete='CASCADE'),
 primary_key=True, autoincrement=False),
 Column('id_meas', Integer, primary_key=True, autoincrement=False),
 ...
 )
 
 view_opts = Table('view_opts', metadata,
 Column('id', Integer, primary_key=True),
 Column('id_view', Integer, ForeignKey('views.id',
 ondelete='CASCADE'),
 nullable=False),
 
 Column('id_cu', Integer, ForeignKey('ctrl_units.id'),
 nullable=False),
 Column('id_meas', Integer, nullable=False),
 ForeignKeyConstraint(('id_cu', 'id_meas'),
  ('sensors.id_cu', 'sensors.id_meas'),
  ondelete='CASCADE'),
 ...
 )
 
 mapped like this:
 
 orm.mapper(Sensor, sensors,
 
 properties={
 'view_opts': orm.relationship(ViewOpt, backref='sensor',
 cascade='all, delete-orphan', passive_deletes=True,
 single_parent=True)
 })
 
 Now when I delete a row from sensor relative view_opt rows are not
 removed.
 
 I can't understand if this depends on DDL, it's a MySQL bug, something
 sqlalchemy related or whatever.
 
 I know I set `passive_deletes=True`to get MySQL ONDELETE CASCADE take
 care of it more and more quickly.
 
 Any help appreciated, thanks for your support
 neurino
 
 -- 
 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.
 

-- 
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.