How does MS-SQL know how to create a 1 to many relationship?

[kamazon]

http://tinyurl.com/258cnx

I created a test database with 3 tables which is viewable in the screenshot listed above:

CUSTOMERS
INVOICES
EMPLOYEES

Sure a Customers to Invoices primary to foreign key relationship is obvious that it's a 1 to many to us, but how did MS-SQL know to create it as a 1 on the customers table to many on the invoices table?? It's not like SQL can think 'well jee 1 customer will want many invoices potentially so I'd better make it a 1 to many.'


Also, when you set a foreign key on a table, you have to have the same column name and type as the primary key, then SQL will always keep those columns in synch right?
In this case with the screenshot provided, do I only have to create a relationship between the 2 tables, can I also just create a primary key and create a foreign key from TransactSQL via command line, and if so, is that the same exact thing? Thanks and sorry for all the newb questions.

 

[r937]

the database doesn't know, you have to tell it

and it doesn't keep foreign keys in synch, unless you are talking about ON UPDATE CASCADE, but updating a primary key isn't something that you run across often as a novice

r937.com | rudy.ca

 

[SQLSister]

When you create a primary key-foreign key relationship, the primary key is the one part of the relationship and the foreign key is the many. You can cascade update - however as rudy said it is a very bad idea to design such that your primary key will change.

Questions about posting. See faq183-874

 

[ESquared]

One-to-one or one-to-many are terms that are descriptive, not prescriptive. It's an observation about the intent of the tables rather than necessarily anything intrinsic to them (although some of it can be enforced).

A primary key/ foreign key relationship always allows a one-to-many relationship: one primary key, referenced in many foreign keys. If the foreign key table has a unique constraint or clustered index on the column, this ensures that the database will enforce a one-to-one relationship between the two tables.

But even in a database where no such unique constraint exists, the relationship between the two tables could still be fairly considered one-to-one (perhaps enforced by the application instead of the database, although this is in my book a Bad Thing) if in practice the foreign key table has no more than one record for every record in the primary key table.

In my experience, having the database enforce the constraint is absolutely required. You can think that you're coding your application so perfectly and well that those extra records could never appear in there, and you want to save some CPU cycles or something not checking about constraints on every data insert.

Wrong. There will eventually be a time, no matter how well you've written, that one of your non-constrainted tables will end up with bad data in it. What I've found is that the constraints actually help me at design time more than they do at customer run time. But all in all, they are indispensable. Just wait until one day you're doing some sort of data repair or mass update at the customer's request and then two weeks later find out you have a mess because of a teensy weensy mistake where you forgot to think about the missing constraints. Or the developer after you did. Or your boss did.

Actually, when we say one-to-one relationship, we often mean a one to zero-or-one relationship. A true one-to-one relationship can't be enforced at the database level because you can't insert to two tables in the same operation. The way I handle this situation is by the use of stored procedures, to ensure both records are properly in place. Alternately, for some database designs, you can do the insert on the foreign key side and use a trigger to automatically create the needed primary key value. But that is for special situations.

Subtyping is an example of a one-to-exactly one relationship that can't be enforced at the database level because the "exactly one" side may be in a different table depending on the type of the primary key row.

-----------------

Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.