How does SQL know when to create a 1 to many relationship by default anyways?

[KAMAZON]

Screenshot

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.

 

[Markbnj]

I don't know what you mean when you say SQL Server "knows" that you have a 1..n relationship between two tables. It doesn't. When you create tables all SQL Server knows about those tables is their record structure. You use constraints to impose relational integrity on the structure.

For example, if you have a table A with a primary key column of type int with the identity property set (so that SQL Server will generate a new unique value for each inserted row), and you have a table B with an int column that you intend to be a foreign key into table A, then you write a constraint (using the GUI to design the table just means that it writes the constraints into the database for you). The constraint in this example will state that B.ForeignKey references A.PrimaryKey. Whether the relationship is 1..1 or 1..n is immaterial to SQL Server. The way the constraint functions in practice is simply to disallow deletions from A when there is a referencing row in B. So if you have a row in A with the ID 1, and one or more rows in B with their ForiegnKey column set to 1, you have to remove the rows in B before you can remove the row in A. The whole purpose is to prevent broken relationships.

The column names don't have to be the same, just referenced correctly in the constraint clause.

 

[WannaFly]

It "knows" because you tell it the columns to use, and if a column is a primary key then it HAS to be the 1 end of the 1..n relation.

As for FK's - the name doesnt matter, but the data type does.

You might want to read up on constraints a little to get a better understanding - all PK's and FK's are are constraints - just like allow null and values, etc.

 

[Markbnj]

Originally posted by: WannaFly
It "knows" because you tell it the columns to use, and if a column is a primary key then it HAS to be the 1 end of the 1..n relation.

As for FK's - the name doesnt matter, but the data type does.

You might want to read up on constraints a little to get a better understanding - all PK's and FK's are are constraints - just like allow null and values, etc.

I would state that a little differently. If a column is typed as a primary key then SQL Server knows it is the identity of the record, but it doesn't know the cardinality of the relation with any other table. It might be 1..n or it might be 1..1.