September 1, 2017 at 1:21 pm
One to Many is easy with a Foreign Key.
How can we create a Zero to Many relationship in SQL Server? Is the answer simply don't use a FK? Or is it a FK with NOCHECK? Something else?
I'd like a FK relationship between the tables, but the child table will be populated before the parent. With a Zero to Many I can still have the relationship between the two.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 1, 2017 at 1:27 pm
But that leaves orphan records. Can you not populate the parent table first?
I supposed you could disable the check constraints, but why would you? Populating the child table and then the parent violates the 1-M relationship, and if you do the inserts from the parent-most record to the child-most, then you don't have that problem. No need to monkey with your constraints.
September 1, 2017 at 1:32 pm
By definition, a relationship connects two or more objects. That means that you have to have at least one object on each side of the relationship, which means that you cannot have a zero to whatever relationship.
You need to create the parent record before creating the child record (if you want a formal relationship). This often means scanning your data twice, once for the parent keys to insert into the parent table and once to insert the child records.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 1, 2017 at 2:05 pm
The child information gets created first as it is configuration data. Parent records might not get created for hours or days after.
Basically I want to create orphans and have them adopted later.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
September 1, 2017 at 2:39 pm
Only thing I can think of is to create an Orphanage row in the parent table to use as a temporary home for the child data until the rightful parent comes along.
September 1, 2017 at 2:46 pm
A permanently disabled FK serves no purpose.
Just a though... Would it be possible to dump the orphans into an orphan table and keep them there unlit the parents are available to take them home.
It seems unlikely that the orphan rows would serve any purpose until they are tied to a parent row any way. When your process inserts a new parent row have it search the orphan table and move the newly adopted rows to the proper table and delete them from the orphan table.
September 1, 2017 at 2:57 pm
Sioban Krzywicki - Friday, September 1, 2017 2:05 PMThe child information gets created first as it is configuration data. Parent records might not get created for hours or days after.
Basically I want to create orphans and have them adopted later.
Then your process is flawed in some way. Without details I can't be more specific.
You should be able to create the parent records before inserting the child records. You may need to go back later and fill in information on the parent record, but you should still be able to create a parent record to enforce the FK relationship.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 2, 2017 at 6:49 am
Can't you just reverse the relationship then? The "child" sure sounds like it's the primary record more than the "parent".
For what it's worth, you could have a nullable FK with NOCHECK so that anything can get added to the table without a relationship. It will work. However, at that point, you've surrendered the very concept of a FK. There won't be any data integrity enforcement and the optimizer won't be able to use it in decisions for execution plans. In that case, why bother with it at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 3, 2017 at 11:51 am
Sioban Krzywicki - Friday, September 1, 2017 1:21 PMOne to Many is easy with a Foreign Key.
How can we create a Zero to Many relationship in SQL Server? Is the answer simply don't use a FK? Or is it a FK with NOCHECK? Something else?
I'd like a FK relationship between the tables, but the child table will be populated before the parent. With a Zero to Many I can still have the relationship between the two.
It is actually many to many relationship
SELECT Customer.Customer_Name, Order.Ordered_Date, Order.Order_Quantity FROM Customer full OUTER JOIN Order ON Customer.CID = Order.CID (syntax generic)September 5, 2017 at 6:02 am
I'm confused. What is the point of creating an order if you don't have a customer who ordered that order?
September 5, 2017 at 7:14 am
anand08sharma - Sunday, September 3, 2017 11:51 AMIt is actually many to many relationshipSELECT Customer.Customer_Name, Order.Ordered_Date, Order.Order_Quantity FROM Customer full OUTER JOIN Order ON Customer.CID = Order.CID (syntax generic)
That's not a "many to many" relationship. A many to many relationship would require a 3rd "bridge" table.
September 5, 2017 at 7:15 am
gvoshol 73146 - Tuesday, September 5, 2017 6:02 AMI'm confused. What is the point of creating an order if you don't have a customer who ordered that order?
I'm guessing this is a web-based order page where the developer of the page thinks it's a good idea to get the order details first, and then take care of customer information, and apparently isn't willing to consider the option of a staging table. Wondering why they would design the web page to NOT use a staging table for order data, especially since web pages are "stateless", and thus it takes effort to keep track of things across different clicks on the page. Using a staging table makes a lot more sense, and you capture perhaps an e-mail address to tie the order data to temporarily, and that way, you can even keep track of the data across sessions, since on many occasions, someone ordering online may get interrupted and need to come back to it later, after their existing session has expired.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 5, 2017 at 7:22 am
sgmunson - Tuesday, September 5, 2017 7:14 AMgvoshol 73146 - Tuesday, September 5, 2017 6:02 AMI'm confused. What is the point of creating an order if you don't have a customer who ordered that order?I'm guessing this is a web-based order page where the developer of the page thinks it's a good idea to get the order details first, and then take care of customer information, and apparently isn't willing to consider the option of a staging table. Wondering why they would design the web page to NOT use a staging table for order data, especially since web pages are "stateless", and thus it takes effort to keep track of things across different clicks on the page. Using a staging table makes a lot more sense, and you capture perhaps an e-mail address to tie the order data to temporarily, and that way, you can even keep track of the data across sessions, since on many occasions, someone ordering online may get interrupted and need to come back to it later, after their existing session has expired.
In other words, they want to store the "Cart" in the Orders table, rather than creating a separate Cart table. Putting the "Cart" before the horse, so to speak.
September 5, 2017 at 7:28 am
gvoshol 73146 - Tuesday, September 5, 2017 7:22 AMsgmunson - Tuesday, September 5, 2017 7:14 AMgvoshol 73146 - Tuesday, September 5, 2017 6:02 AMI'm confused. What is the point of creating an order if you don't have a customer who ordered that order?I'm guessing this is a web-based order page where the developer of the page thinks it's a good idea to get the order details first, and then take care of customer information, and apparently isn't willing to consider the option of a staging table. Wondering why they would design the web page to NOT use a staging table for order data, especially since web pages are "stateless", and thus it takes effort to keep track of things across different clicks on the page. Using a staging table makes a lot more sense, and you capture perhaps an e-mail address to tie the order data to temporarily, and that way, you can even keep track of the data across sessions, since on many occasions, someone ordering online may get interrupted and need to come back to it later, after their existing session has expired.
In other words, they want to store the "Cart" in the Orders table, rather than creating a separate Cart table. Putting the "Cart" before the horse, so to speak.
Yep. I'd very strongly recommend they use a staging table for this purpose.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply