Optional relationship in SQL Server -- How to implement in SSMS Schema Designer

Question

I have two cases where I would like to set 'optional' relationship inside one table or between two tables.

First:

I want to set this relation as optional, in other words: there will be categories and subcategories in one table, so subcategory column is only optional.

Second:

Here I want to make sure that House cannot exist without Member (mandatory relation), but Member can be without any House (optional relation).

I don't have any experience with Microsoft SQL Server and I don't have idea how can I modify these relations.

Here is the end result in Oracle diagram:

Answer 1

If you're asking how this can be done in the schema designer (DB Diagram) in SQL Management Studio, it's easy:-

1. Right-click the table in the digram, choose 'Table View' -> 'Standard'

2. In the 'Allow Nulls' column, set the optional Foreign Keys to 'Allow Nulls'

3. Press Ctl + S to save the updates to the schema.

Additional details about the selected object in the designer (column, table, FK etc.) are available by viewing the Properties window (usually in the right of the screen). For example, here are the properties for the subcat column in the example: