How to Create a real one-to-one relationship in SQL Server

by

I’m pretty sure it is technically impossible in SQL Server to have a True 1 to 1 relationship, as that would mean you would have to insert both records at the same time (otherwise you’d get a constraint error on insert), in both tables, with both tables having a foreign key relationship to each other.

That being said, your database design described with a foreign key is a 1 to 0..1 relationship. There is no constraint possible that would require a record in tableB. You can have a pseudo-relationship with a trigger that creates the record in tableB.

So there are a few pseudo-solutions

First, store all the data in a single table. Then you’ll have no issues in EF.

Or Secondly, your entity must be smart enough to not allow an insert unless it has an associated record.

Or thirdly, and most likely, you have a problem you are trying to solve, and you are asking us why your solution doesn’t work instead of the actual problem you are trying to solve (an XY Problem).

UPDATE

To explain in REALITY how 1 to 1 relationships don’t work, I’ll use the analogy of the Chicken or the egg dilemma. I don’t intend to solve this dilemma, but if you were to have a constraint that says in order to add a an Egg to the Egg table, the relationship of the Chicken must exist, and the chicken must exist in the table, then you couldn’t add an Egg to the Egg table. The opposite is also true. You cannot add a Chicken to the Chicken table without both the relationship to the Egg and the Egg existing in the Egg table. Thus no records can be every made, in a database without breaking one of the rules/constraints.

Database nomenclature of a one-to-one relationship is misleading. All relationships I’ve seen (there-fore my experience) would be more descriptive as one-to-(zero or one) relationships.

More Related Contents:

  1. Solution for: Store update, insert, or delete statement affected an unexpected number of rows (0) [closed]
  2. The object ‘DF__*’ is dependent on column ‘*’ – Changing int to double
  3. CROSS JOIN vs INNER JOIN in SQL
  4. SQL Server: Get table primary key using sql query [duplicate]
  5. How to determine the number of days in a month in SQL Server?
  6. how to declare global variable in SQL Server..?
  7. Must declare the scalar variable
  8. How to get the identity of an inserted row?
  9. Finding a Top Level Parent in SQL
  10. How do I list all the columns in a table?
  11. Script entire database SQL-Server
  12. Why is 30 the default length for VARCHAR when using CAST?
  13. Editing Record issues in Access / SQL (Write Conflict)
  14. SQL UPDATE TOP with ORDER BY?
  15. How do I create a table alias in MySQL
  16. WHERE IS NULL, IS NOT NULL or NO WHERE clause depending on SQL Server parameter value
  17. case statement in SQL, how to return multiple variables?
  18. How do I execute a MS SQL Server stored procedure in java/jsp, returning table data?
  19. MSSQL Regular expression
  20. SQL Server updating a time stamp column
  21. Calculate exact date difference in years using SQL
  22. SQL can I have a “conditionally unique” constraint on a table?
  23. SUM total time in SQL Server [duplicate]
  24. Count number of NULL values in each column in SQL
  25. Count consecutive duplicate values in SQL
  26. sql server displaying missing dates
  27. Memory effective way to read BLOB data in C#/SQL 2005
  28. How to improve performance for datetime filtering in SQL Server?
  29. can we have a foreign key which is not a primary key in any other table?
  30. How to insert Arabic characters into SQL database?

Leave a Comment