plexnowbot

• Ms Access Pk Fk Ak Form

When we want to implement relationship between two tables then we use concept of foreign key. It is also known as referential integrity. We can create more than one foreign key per table. Foreign key is generally a primary key from one table that appears as a field in another where the first table has a relationship to the second.

1. Feb 19, 2018 - Learn when to use a natural key and when to use a surrogate key in your database. Might recall, a primary key is a unique identifier given to a record in our database. AR Invoices: INV-123. If a natural key is recommended, use a surrogate key field as the primary key, and a natural key as a foreign key.
2. This shouldn't be caused by column name mismatch. However EF doesn't support foreign keys that refer to unique indexes, and not the primary key. For EF each FK must refer to the PK. EG this is legal in SQL Server: create table a(id int primary key, ak int unique) create table b(id int primary key, aak int references a(ak)) but not supported by EF.

I have two tables in two different databases. In table1 (in database1) there is a column called column1 and it is a primary key. Now in table2 (in database2) there is a column called column2 and I want to add it as a foreign key.

I tried to add it and it gave me the following error:

Msg 1763, Level 16, State 0, Line 1
Cross-database foreign key references are not supported. Foreign key Database2.table2.

Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

How do I do that since the tables are in different databases.

6 Answers

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:

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.

Ms Access Pk Fk Ak Form

John HartsockJohn Hartsock

If you need rock solid integrity, have both tables in one database, and use an FK constraint. If your parent table is in another database, nothing prevents anyone from restoring that parent database from an old backup, and then you have orphans.

This is why FK between databases is not supported.

A-KA-K

In my experience, the best way to handle this when the primary authoritative source of information for two tables which are related has to be in two separate databases is to sync a copy of the table from the primary location to the secondary location (using T-SQL or SSIS with appropriate error checking - you cannot truncate and repopulate a table while it has a foreign key reference, so there are a few ways to skin the cat on the table updating).

Then add a traditional FK relationship in the second location to the table which is effectively a read-only copy.

You can use a trigger or scheduled job in the primary location to keep the copy updated.

Cade RouxCade Roux

You could use check constraint with a user defined function to make the check. It is more reliable than a trigger. It can be disabled and reenabled when necessary same as foreign keys and rechecked after a database2 restore.

Walery Strauch

The short answer is that SQL Server (as of SQL 2008) does not support cross database foreign keys--as the error message states.

While you cannot have declarative referential integrity (the FK), you can reach the same goal using triggers. It's a bit less reliable, because the logic you write may have bugs, but it will get you there just the same.

See the SQL docs @ http://msdn.microsoft.com/en-us/library/aa258254%28v=sql.80%29.aspx Which state:

Triggers are often used for enforcing business rules and data integrity. SQL Server provides declarative referential integrity (DRI) through the table creation statements (ALTER TABLE and CREATE TABLE); however, DRI does not provide cross-database referential integrity. To enforce referential integrity (rules about the relationships between the primary and foreign keys of tables), use primary and foreign key constraints (the PRIMARY KEY and FOREIGN KEY keywords of ALTER TABLE and CREATE TABLE). If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution and prior to the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed (fired).

There is also an OK discussion over at SQLTeam - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=31135

EBarrEBarr

As the error message says, this is not supported on sql server.The only way to ensure refrerential integrity is to work with triggers.

JanJan

Not the answer you're looking for? Browse other questions tagged sqlsql-servertsqlforeign-keys or ask your own question.