I am wondering if it's possible to change the firebird engine to
use table
constraints for foreign keys rather than individual column references.
I'm also wondering if this will be an issue for other databases.
I have compound (two part) primary keys.
Table definitions, for example, looks somewhat like this (not all
columns shown):
# table Tcoordinator
# Table of coordinators
Tcoordinator = Table('coordinator',
Column('system_id',
Integer,
primary_key=True,
nullable=False,
),
Column('id',
Integer,
Sequence("coordinator_id", optional=True),
primary_key=True,
nullable=False,
),
Column('first_name',
String(16),
nullable=False,
),
Column('last_name',
String(24),
nullable=False,
),
)
# table Torg
# The organization table keeps related professional contacts together.
Torg = Table('org',
Column('system_id',
Integer,
primary_key=True,
nullable=False,
),
Column('id',
Integer,
Sequence("org_id", optional=True),
primary_key=True,
nullable=False,
),
Column('name',
String(32),
nullable=False,
index="org_name_idx",
),
Column('add_date',
DateTime,
nullable=False,
),
Column('update_date',
DateTime,
nullable=False,
),
Column('add_user_id',
Integer,
ForeignKey("coordinator.id"),
),
Column('add_user_system_id',
Integer,
ForeignKey("coordinator.system_id"),
),
)
sqlalchemy produces this sql to create the TOrg table:
CREATE TABLE org(
system_id INTEGER NOT NULL,
id INTEGER NOT NULL,
name VARCHAR(32) NOT NULL,
add_date DATE NOT NULL,
update_date DATE NOT NULL,
add_user_id INTEGER REFERENCES coordinator(id),
add_user_system_id INTEGER REFERENCES coordinator(system_id),
update_user_id INTEGER REFERENCES coordinator(id),
update_user_system_id INTEGER REFERENCES coordinator
(system_id),
org_type INTEGER NOT NULL REFERENCES org_type(id),
postal_address1 VARCHAR(32),
postal_address2 VARCHAR(32),
postal_city VARCHAR(20),
postal_state VARCHAR(2),
postal_zip VARCHAR(10),
telephone VARCHAR(16),
description VARCHAR(4096),
PRIMARY KEY (system_id, id)
)
This gives me an error from firebird, because it knows that the
cordinator table
doesn't have an index on id or system_id (instead, it has an index
on both
columns together)
Currently, I generate my database using this sql, which I'd like to
figure out how to
get the firebird engine to generate too:
create table org
(
system_id integer not null ,
id integer not null ,
name varchar(32) not null ,
add_date timestamp not null ,
update_date timestamp not null ,
add_user_id integer ,
add_user_system_id integer ,
update_user_id integer ,
update_user_system_id integer ,
org_type integer not null ,
postal_address1 varchar(32) ,
postal_address2 varchar(32) ,
postal_city varchar(20) ,
postal_state varchar(2) ,
postal_zip varchar(10) ,
telephone varchar(16) ,
primary key (system_id, id)
);
-- begin constraints for table org
alter table org add constraint
org_add_user_fk
foreign key(add_user_system_id, add_user_id)
references coordinator(system_id, id)
on update cascade on delete set null
;
alter table org add constraint
org_update_u_fk
foreign key(update_user_system_id, update_user_id)
references coordinator(system_id, id)
on update cascade on delete set null
;
alter table org add constraint
org_org_type_fk
foreign key(org_type)
references org_type(id)
on update cascade on delete cascade
;
Also, it seems that ForeignKey doesn't allow me to specify delete
and update
cascade rules.. Maybe I missed that in the docs..
--
Brad Clements, [EMAIL PROTECTED] (315)268-1000
http://www.murkworks.com
AOL-IM or SKYPE: BKClements
-------------------------------------------------------
Using Tomcat but need to do more? Need to support web services,
security?
Get stuff done quickly with pre-integrated technology to make your
job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache
Geronimo
http://sel.as-us.falkag.net/sel?
cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
