New answers tagged

1

Just add an ON DELETE CASCADE option to your foreign key: ALTER TABLE links_link DROP CONSTRAINT constraint_name, ADD CONSTRAINT constraint_name FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON DELETE CASCADE; Apparently in django the above is translated as: on_delete=models.CASCADE


0

when you define primary key on column sql server implicitly define cluster index on that column and indexes are faster when you use integer datatype,alphanumeric is little bit slow searching than integer column. production_order_table:- production_order int(pk) product_id int(fk) define non-cluster index Product_table:- product_id int(pk) product_code ...


4

I don't think that - a default index generation for foreign key columns - would lead to serious problems. It was just a decision taken from the PostgreSQL developers, to leave this choice to each database designer / administrator. We have the choice to either add an index when creating a foreign key or not. If they had taken the opposite decision, then ...


1

It could be related to the value of foreign_key_checks variable. Try the following statement, and check the value: mysql> show variables like 'foreign_key_checks'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | foreign_key_checks | ON | +--------------------+-------+


0

Not sure what version of db2 you are using, but if it's LUW you can use: select enforced from syscat.tabconst where tabschema = ? and tabname = ? and constname = ? FWIW, it is better to use the syscat views (vs sysibm), since they are more stable between versions.


1

I had the same problem. I set all foreign keys not enforced, then I loaded the tables and set the foreign keys enforced. Several fail. I do a query: SELECT TABSCHEMA, TABNAME, STATUS FROM SYSCAT.TABLES where status = 'C' For the tables, that are shown, I do a: set integrity for table immediate checked This works. Afterwards I can set all foreign keys ...


1

If you really want id_Products in Options for performance reasons (you do not need it there to ensure data integrity; indeed it confuses that argument) you can do so. Make the foreign key in Options a multi-column key. Make it point to the corresponding columns in Variants. Even if Variants.Id is unique across all products there is no risk to the data by ...


2

You can make use of an EXCLUSION constraint and tsrange datatype instead of two timestamps. create table trip_segments ( segment_id serial primary key, travel_ts tstzrange, -- departure time to arrival time trip_id integer references trips (trip_id), EXCLUDE USING gist (trip_id WITH =, travel_ts WITH &&) ); -- first segment insert into ...


2

You'll have your reasons for going this route, where it seems you're duplicating information (since id_Products is implied from id_Variants). However, unless what you want to achieve is having Options with an optional product OR variant (i.e. a CHECK constraint ensuring one and only one of these IDs is not null), you must also ensure that you don't get into ...


2

Foreign keys Foreign keys are good to keep the database consistent. The common practice is to name the foreign key starting with the name that it refers to then the name of the column: instead of id_contact use contact_id. ID column There are a couple of benefits of serial primary key. Faster insert speed Easier to refer to the row if necessary Some ...


0

The primary purpose of FOREIGN KEYs is to maintain referential integrity. Check out any of the standard textbooks (Connolly, Elmasri, Garcia-Molina or Silberschatz amongst others). This despite users' and programmers' best efforts to produce systems which render the concept null and void!


1

A foreign key null means that there may not be an entry on the other side. In a master-detail relationship it means a detail can exist without a master.


0

SELECT a.foo, b.bar FROM db1.tableA AS a JOIN db2.tableB AS b ON a.something = b.whatever; This assumes both "databases" are on the same instance of MySQL on the same server. Otherwise, you need to reword the question.


2

You have the FOREIGN KEY relationships defined in the wrong direction. They should be the other way around, from medias to ads. The added UNIQUE constraint on medias (ad_id, media_type_id) takes care of the restriction that an ad can have only one media, for every media_type. This way, you can easily add more media types, just by inserting a new row in ...



Top 50 recent answers are included