Implementing one-to-zero-or-one relation in SQL Server

sql-server sql-server-2008 entity-framework-4.1 entity-relationship relation
Mostafa picture Mostafa · Oct 4, 2011 · Viewed 25.8k times · Source

I'm using Entity Framework 4.1 database first approach. I've used legacy database. In my edmx file which created entity classes based on tables in the legacy database, there is a one-to-zero-or-one association between some entities.

Although I explored the tables of database and relation between them I didn't find out how one-to-zero-or-one relation have been implemented in database.

For more information I put some screenshots of my database diagram and the property of its relation and correspondent entities in edmx file:

enter image description here enter image description here

Answer

Ladislav Mrnka picture Ladislav Mrnka · Oct 4, 2011

The 1-0..1 relation in your database is directly visible. It is built between Course and OnlineCourse tables where Course is principal in relation (1) and OnlineCourse is dependent with FK configured on CourseID. FK is also PK of the OnlineCourse = it must be unique and because of that it is 0..1.

Database "always" uses 1 - 0..1 because real 1 - 1 cannot be effectively used for data insertion. 1 - 1 means that left must be inserted after right but right must be inserted after left = impossible. Because of that 1 - 0..1 is used where left is principal and must be inserted before right and right is dependent and must be inserted after left.

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More