Hot answers tagged

6

You are right, the design allows inconsistencies, exactly what you notice. A ProjectRealm may be referring through Project to a company and through CompanyRealm to another company. This is not uncommon, it appears when there is a triangular or a "diamond" shape in the relationships: Realm Company \ / \ \ / \ ...


3

In Postgres, you could use the basic table definitions you have (slightly different syntax) and add a unique functional index using COALESCE(): CREATE UNIQUE INDEX entity_4attr_uni ON entity(attr_a_id , attr_b_id , COALESCE(attr_c_id, 0) , ...


3

There isn't an easy way (except in SQL Server, see below) to enforce these constraints. And I say "these" and not "this" because they are indeed more than one. You want to enforce uniqueness on (a,b,c,d) when all the attributes are not null. And uniqueness on (a,b,c) when d is null. And uniqueness on (a,b,d) when c is null. And uniqueness on (a,b) when both ...


2

ORA-02256: number of referencing columns must match referenced columns Cause: The number of columns in the foreign-key referencing list is not equal to the number of columns in the referenced list. Action: Make sure that the referencing columns match the referenced columns. The problem is the foreign key should reference both columns used in the ...


2

A foreign key means "ensure the values in this column exist in another column" For example create table sales_orders ( order_id int primary key, customer_id int, ... ); If customer_id does not have a foreign key (to table customers), a user could enter a value that does not point to a real customer. But with a foreign key, the value in customer_id ...


1

You could create local copies of the remote tables, and run periodic processes that would add new records, remove deleted records, and update changed records. How large are the remote tables? I guess not too big since you were planning on creating local materialized views from them. Normal tables that you keep synchronised would have a similar impact. Once ...


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

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



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