4

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

First:

enter image description here

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:

enter image description here

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:

enter image description here

CJBS
  • 15,147
  • 6
  • 86
  • 135
Gregy
  • 340
  • 1
  • 6
  • 16
  • Just make the optional FK columns NULLable, and the mandatory ones NOT NULLable. – Tab Alleman Jun 19 '15 at 18:48
  • @TabAlleman I think he's already got that -- from those Oracle diagrams, I think the red star indicates that the field is NOT NULL, hence lack of a star would be NULLable. – CJBS Jun 19 '15 at 18:49
  • I don't know Oracle, but if he's already got that, then what is left to be missing? – Tab Alleman Jun 19 '15 at 18:52
  • @Gregy - I gather that you've used the SQL Server designer to create this schema, and you're asking how to use it to set the optional Foreign Key relationships as NOT NULL in the designer? – CJBS Jun 19 '15 at 18:52
  • To create diagrams I have used Microsoft SQL Server Management Studio. The last one is from my database in Oracle environment. I think that you are right with setting FK as NOT NULL, I was just confused because of visual relationship line presentation. – Gregy Jun 19 '15 at 19:00

1 Answers1

2

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'

Right-clicking the table

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

Setting NULLable in designer

  1. 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:

subcat properties

CJBS
  • 15,147
  • 6
  • 86
  • 135
  • You are 100% right, I have made it exactly same way, but I though that I missed something because of relationship visualisation. – Gregy Jun 19 '15 at 19:07
  • It's a little strange that just the column names appear in the default view when creating a DB diagram (at least that's my experience). It would be easier if the Standard view was shown. – CJBS Jun 19 '15 at 19:09
  • Thank you a lot for support :) – Gregy Jun 19 '15 at 19:23