1

After lots of reading, I decided to make surrogate keys for my tables. Now I have a dilemma on creating a 1:1 relationship on two tables, "Orders" and "Form_VSA_albums". 1:1 because one form can only have one order and one order can only have one form. Note, there CAN be orders with NO form but there CANNOT be a from with no order so the Order table is the parent table and it holds the PK.

When using Natural keys, I made the company's Order_id the PK of both tables and I was going to make this value a PROGRAM-GENERATED varchar(5) (so I don't have to worry about user error entries messing up the PK - a main issue when using natural keys). So each time an Order would need a Form, the ID of the new FORM table row would get the same ID of the ORDER row. No problems.

But now that I am switching to surrogate keys, the PK of both tables will be computer generated and I have no control of the values being generated when Order and Form rows are being created. So my question is, what can I do to make sure that when an Order needs to create a new Form, that the PK of the Form should be the SAME as the PK of the calling Order? If it's not the same it will violate the 1-1 relation so it will fail.

p.s. The company's internal order_id that I wanted to use as a natural key, I now moved to a new attribute called order_id - varchar(5)).

enter image description here

Zvi Twersky
  • 399
  • 1
  • 5
  • 25
  • You have to populate `Orders` first. Once this is done you have the natural key, which you can use to lookup the surrogate. An insert query, with a from clause, would do it. – David Rushton Dec 21 '16 at 09:37
  • Yes, I didn't think a lookup would be an issue. The "insert query, with a from clause" was the answer I was looking for. So it will allow me to change the Surrogate PK of the form table before updating the table you are saying? Because as I know... you are not supposed to change PKs - ever - and I am doing this here. – Zvi Twersky Dec 21 '16 at 10:20

2 Answers2

1

Form should be the SAME as the PK of the calling Order? If it's not the same it will violate the 1-1 relation so it will fail.

You wouldn't. Each table would have its own primary key value, and the Form would have a foreign key to the order table's primary key.

You would enforce the 1:1 relationship by adding a unique key to the foreign key column in the Form table.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • I read about this already and had tried to add a FK and making its datatype a uniqueidentifier but SQL sever would not allow a relation because the datatype of the PK in orders was int. Maybe there's another way to make a FK Unique that I'm missing...? Are you talking about a unique constraint? (which I would have to read about) – Zvi Twersky Dec 21 '16 at 10:28
  • Both the unique and the foreign key constraints are applied to the column of the Form table that holds the Order table's primary key value. – David Aldridge Dec 21 '16 at 10:39
  • So I make a field in Form table and call it OrderID and make it int. Now what? If I drag the OrderID from the Order table to the Form table, it will be a regular FK which will make it 1:M. How do I make this FK Unique so that it's a 1:1? – Zvi Twersky Dec 21 '16 at 11:19
  • http://stackoverflow.com/questions/64981/sql-server-2005-how-create-a-unique-constraint – David Aldridge Dec 21 '16 at 11:21
  • ok, that made it Unique and is giving it 1:1. I will need to test this via data entries but thanks. – Zvi Twersky Dec 21 '16 at 11:27
1

In a well-designed database, tables will have surrogate keys about half the time. However, don't fall into the trap of thinking that just because something works perfectly well in most general cases, it should be adopted in all cases. Examine what this is doing to your own design. You have a perfectly good design with the same key value being used to enforce a 1-1 relationship. But your dogmatic desire to force a unique surrogate key into every table means you must dismiss a perfectly fine method of enforcing the relationship. An effective alternate will not be easy and will inevitably force you to get much more involved in maintaining the integrity of the relationship, as you have already noticed, and the effects could very well cascade into other compromises in design.

The solution is simple: recognize that any rule or aspect of design, though it may be the perfect solution in most instances, cannot be the perfect solution in all instances. You currently have a design that is simple, elegant and does everything you need it to do. Stick to it.

And always keep more than one tool in your toolbox. Your designs will be better for it.

TommCatt
  • 5,498
  • 1
  • 13
  • 20