Hot answers tagged

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


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


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

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


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


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

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


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


1

When you say "a list of tables which reference this table" do you mean a list of tables that have a foreign key that reference your primary key? If so you can get a list of foreign keys that reference your table 'User' with the following query: SELECT name as Foreign_Key ,schema_name(schema_id) as Schema_Name ,object_name(parent_object_id) as Table_Name ...


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.



Only top voted, non community-wiki answers of a minimum length are eligible