New answers tagged

0

The PostgREST team gave a potential solution: faking the foreign key constraints. They read the pg_catalog.pg_constraint table to get this information. The idea was that I could manipulate the search_path and create my own pg_constraint table where I could make the fake foreign keys: CREATE TABLE pg_constraint (LIKE pg_catalog.pg_constraint); ALTER ...


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


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


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


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

A foreign key ensures that a row in a primary table is not deleted if it's referenced by other tables and therefore it guarantees data integrity. See https://technet.microsoft.com/en-us/library/ms175464(v=sql.105).aspx


-1

The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted. check this page please. Of course we can join two tables, but JOIN will not ensure referential integrity.


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


0

sp_help 'User' This command will give you all constraints and dependencies on this table. Further, if you also want all the procedures which are dependent on this table, let me know.


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



Top 50 recent answers are included