This new order create new Order Header record (one dor each Purchase), and this purchase include several orders.įor example (from the original data): Vendor ID 1492, Make a Purchase that got the PurchaseOrderID = 9 (new purchase at that time). I'm not sure I understand what you meanĪ Vendor can order something. Please clarify this point if things are still not understood. So where I see a field name with a id added to the end without a key, there is a table with that name that has aĠ to many relationship and separates table operations between purchase order and vendor? Therefor the Order is related to the Vendor (No separation). One-to-many relationship (One-to-many do not allow to have zero-to-many, you can not have a purchase order without a Vendor, as the VendorID is "Not null"). You can not delete the vendor if he has Purchase Orders as this is "associated" which that Vendor (unless you delete the Purchase Orders first). as mentioned you just enter a new order for that Vendor. So does this enable separation between the vendor and purchase orders
You can get all the Purchases of a specific Vendor. If the new Vendor has a new order/orders then you do not update, but insert a new order with this BusinessEntityID. Theoretically, the order BusinessEntityID should not be updated after it is created. The PurchaseOrderHeader Table Contains purchase order information. If the vendor has a purchase order then you update the purchase order with the BusinessEntityID from vendor True, You can insert new vendor: insert Purchasing.Vendor (BusinessEntityID,AccountNumber,Name,CreditRating,PreferredVendorStatus,ActiveFlag,PurchasingWebServiceURL,ModifiedDate)ģ000,'AUSTRALI0001X','Australia Bike Retailer2X',1,1,1,NULL,' 00:00:00.000' So this type of relationship can have a vendor and no purchase orders I see that relationship in the table properties CHECK CONSTRAINT ĮXEC sys.sp_addextendedproperty key constraint referencing Employee.EmployeeID.', FK_PurchaseOrderHeader_ShipMethod_ShipMethodIDĮXEC sys.sp_addextendedproperty key constraint referencing ShipMethod.ShipMethodID.', FK_PurchaseOrderHeader_Vendor_VendorIDĮXEC sys.sp_addextendedproperty key constraint referencing Vendor.VendorID.', From the PurchaseOrderDetail DDL - FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderIDĮXEC sys.sp_addextendedproperty key constraint referencing PurchaseOrderHeader.PurchaseOrderID.', hope this explain what you need * From the PurchaseOrderHeader DDL: - FK_PurchaseOrderHeader_Employee_EmployeeIDĪLTER TABLE. In the window that open you can check each key. The DDL of table PurchaseOrderDetail will give you more info on the first key. * You can get better info on the last 3, from the DDL Script of the PurchaseOrderHeader table. Right click on PurchaseOrderHeader table -> Relationships In addition to RSingh's post, There are 4 Foreign Key related to PurchaseOrderHeader:įK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderIDįK_PurchaseOrderHeader_Employee_EmployeeIDįK_PurchaseOrderHeader_ShipMethod_ShipMethodID