Finally figured this out from this post. The PRAGMA foreign_key setting does not persist but you can set it every time the connection is made in the ConnectionString. This allows you to use Visual Studio’s table adapters. Make sure you have the latest version (1.0.73.0) of system.data.sqlite installed (1.0.66.0 will not work). Change your ConnectionString … Read more
There is a tiny performance hit on inserts, updates and deletes because the FK has to be checked. For an individual record this would normally be so slight as to be unnoticeable unless you start having a ridiculous number of FKs associated to the table (Clearly it takes longer to check 100 other tables than … Read more
Inspired by Eevee’s comment: def get_class_by_tablename(tablename): “””Return class reference mapped to table. :param tablename: String with name of table. :return: Class reference or None. “”” for c in Base._decl_class_registry.values(): if hasattr(c, ‘__tablename__’) and c.__tablename__ == tablename: return c
CHECK constraints cannot currently reference other tables. The manual: Currently, CHECK expressions cannot contain subqueries nor refer to variables other than columns of the current row. One way is to use a trigger like demonstrated by @Wolph. A clean solution without triggers: add redundant columns and include them in FOREIGN KEY constraints, which are the … Read more
As already implied in @gareth’s answer, hard-coding a default id value might not always be the best idea: If the id value does not exist in the database, you’re in trouble. Even if that specific id value does exist, the corresponding object may change. In any case, when using a hard-coded id value, you’d have … Read more
Sure, just add blank=True, null=True for each field that you want to remain optional like subject = models.ForeignKey(subjects, blank=True, null=True)
It’s not strictly needed for the query, it’s true. It exists for several reasons: As a constraint on the table to stop you inserting something that doesn’t point to anything; As a clue for the optimizer; and For historical reasons where is was more needed. (1) is probably the important one of the three. This … Read more
If you want to drop foreign key if it exists and do not want to use procedures you can do it this way (for MySQL) : set @var=if((SELECT true FROM information_schema.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE() AND TABLE_NAME = ‘table_name’ AND CONSTRAINT_NAME = ‘fk_name’ AND CONSTRAINT_TYPE = ‘FOREIGN KEY’) = true,’ALTER TABLE table_name drop foreign key … Read more
In Oracle, you could do this: create table D ( ID numeric(1), CODE varchar(2), constraint PK_D primary key (ID, CODE) );
It would help if you posted the error message. But I think you are just missing the parenthesis: ALTER TABLE my_table ADD CONSTRAINT my_fk FOREIGN KEY (my_field) REFERENCES my_foreign_table ON DELETE CASCADE;