Add Foreign Key relationship between two Databases
You would need to manage the referential constraint across databases using a Trigger.
Basically you create an insert, update trigger to verify the existence of the Key in the Primary key table. If the key does not exist then revert the insert or update and then handle the exception.
Example:
Create Trigger dbo.MyTableTrigger ON dbo.MyTable, After Insert, Update
As
Begin
If NOT Exists(select PK from OtherDB.dbo.TableName where PK in (Select FK from inserted) BEGIN
-- Handle the Referential Error Here
END
END
Edited: Just to clarify. This is not the best approach with enforcing referential integrity. Ideally you would want both tables in the same db but if that is not possible. Then the above is a potential work around for you.
Having foreign keys between two different databases using linked servers?
No, it is not possible to create foreign keys between objects in different databases (even if they are on the same server). The official documentation is pretty clear about that:
FOREIGN KEY constraints can reference only tables within the same database on the same server. Cross-database referential integrity must be implemented through triggers. For more information, see CREATE TRIGGER (Transact-SQL).
It even points you to the possible workaround, i.e. to try to implement some kind of referential integrity checks using triggers. You can add after insert/update triggers on both sides to validate the data changes, and after delete triggers on the primary table to check are there child records. If the validation fails, you will raise an error. You can also use instead of triggers.
But the solution with triggers will not guarantee the referential integrity anyway. You can lose connectivity between databases. You can restore one of the databases from older backup. All kind of things can go wrong. You better try to reconsider your database design. Is it possible to combine these two databases into one? Is it possible to maintain copies of both tables into each of the databases and try to replicate stuff?
Using column name as part of foreign key relationship
You can have a constant stored computed column for the FK.
CREATE TABLE clients
(
id int,
table_id int,
status TEXT,
refcolname text GENERATED ALWAYS AS ('status') STORED,
FOREIGN KEY (table_id, refcolname, status)
REFERENCES dropdowns (table_id, field_name, value)
);
Really I would advice against the design like that. Why to create a united table of lookups just to get troubles referencing its parts. Use the EAV pattern only if it is absolutely inevitable, for example a set of lookup tables is to be defined by a user at runtime.
How to set Foreign Key in mysql between Two different databases?
Any relationship between/among tables are confined within a schema. You cannot define foreign key constrain between two unrelated tables in two different schema. If you have a real need o do it then you need to re-think about your database design.
Many-to-Many Relationship between two tables in two different databases
The standard way of using foreign key constraints to enforce referential integrity is only possible within the same database - not db cluster. But you can operate across multiple schemas in the same database.
Other than that, you can create tables just the same way. And even join tables dynamically among remote databases using dblink or FDW. Referential integrity cannot be guaranteed across databases by the RDBMS, though.
Does not matter much whether the other DB is on the same physical machine or even in the same DB cluster - that just makes the connection faster and more secure.
Or you can replicate data to a common database and add standard constraints there.
PostgreSQL FOREIGN KEY with second database
I've not had occasion to use this myself, but you might want to look into Foreign Data Wrappers, which are essentially the successor to dblink
. In particular, postgres-fdw.
Once the general setup of the fdw is in place (steps 1-3 in the link above), you could create a foreign table via CREATE FOREIGN TABLE
, defined like the table in your remote DB, and then use that table as part of the foreign key CONSTRAINT
, and see if it works.
If that doesn't work, another option would be to have a process which ETL's the data (say, via a Python script) from the remote server over to the local server (say, on an hourly or daily basis, depending on the size), and then you would have a true local table to use in the foreign key CONSTRAINT
. It wouldn't be real-time, but depending on your needs, may suffice.
Failed to create table because of foreign key constraints
The foreign key should be on the movie_genre table, not the genres table
Add multiple primary keys to a table
I find one solution.
select OBJECT_NAME(OBJECT_ID) AS NameofConstraint
FROM sys.objects
where OBJECT_NAME(parent_object_id)='avgEnt'
and type_desc LIKE '%CONSTRAINT'
Find constraint of table PK_avgent
After drop primary on table
-- drop current primary key constraint
ALTER TABLE dbo.avgEnt
DROP CONSTRAINT PK_avgent;
GO
After add two column
-- create new primary key constraint
ALTER TABLE dbo.avgEnt
ADD CONSTRAINT PK_avgent PRIMARY KEY NONCLUSTERED (SrNo, TrnDate);
GO
it is work form me.
Related Topics
Fast Way to Discover the Row Count of a Table in Postgresql
Custom Serial/Autoincrement Per Group of Values
How to Select from Subquery Using Laravel Query Builder
Table-Less Union Query in Ms Access (Jet/Ace)
Mixing Ansi 1992 Joins and Commas in a Query
SQL Query to Get Aggregated Result in Comma Separators Along With Group by Column in SQL Server
How to Find All Connected Subgraphs of an Undirected Graph
Stored Procedure With Optional "Where" Parameters
Exporting Data from SQL Server Express to CSV (Need Quoting and Escaping)
How to Assign an Exec Result to a SQL Variable
Is There a Max Function in SQL Server That Takes Two Values Like Math.Max in .Net
Is There a Shortcut For Select * From
MySQL Delete from With Subquery as Condition
Microsoft Jet Wildcards: Asterisk or Percentage Sign
SQL Selecting from Two Tables With Inner Join and Limit
Group by and Aggregate Sequential Numeric Values