February 14, 2008 at 7:48 am
Good Morning Everyone,
I have a question regarding a child table with two parent tables.
Consider the following three tables, which I've simplified here:
(1) Supplier:
CREATE TABLE Supplier
(
SupplierID INT NOT NULL IDENTITY(1,1)
CONSTRAINT PK_Supplier_SupplierID PRIMARY KEY CLUSTERED ([SupplierID] ASC),
S_CompanyName VARCHAR(50) NOT NULL
-- (Some other fields here)
)
(2) Wholesaler:
CREATE TABLE Wholesaler
(
WholesalerID INT NOT NULL IDENTITY(1,1)
CONSTRAINT PK_Wholesaler_WholesalerID PRIMARY KEY CLUSTERED ([WholesalerID] ASC),
W_CompanyName VARCHAR(50) NOT NULL
--(Some other fields here)
)
Note that the rest of the Supplier and Wholesaler tables are different enough to warrant them being separate.
(3) Contact
The Contact table is the one I'm wondering about.
When I create it, I will obviously assign it a ContactID, which will be the primary key, but then how to I go about relating Contact records to EITHER the Supplier or Wholesaler tables - but never both?
Would it be something like this:
CREATE TABLE Contact
(
ContactID INT NOT NULL IDENTITY(1,1)
CONSTRAINT PK_Contact_ContactID PRIMARY KEY CLUSTERED ([ContactID] ASC),
SupplierID INT NULL
CONSTRAINT FK_Contact_SupplierID FOREIGN KEY ([SupplierID]) REFERENCES Supplier([SupplierID]),
WholesalerID INT NULL
CONSTRAINT FK_Contact_WholesalerID FOREIGN KEY ([WholesalerID]) REFERENCES Wholesaler([WholesalerID]),
FName VARCHAR(50) NULL,
LName VARCHAR(50) NULL,
-- Some other fields here
/* Add table-level CHECK constraint to make sure that both SupplierID and WholesalerID are not empty. */
CONSTRAINT CK_Contact_ParentID CHECK (([SupplierID] IS NOT NULL) OR ([WholesalerID] IS NOT NULL))
)
Any input/feedback would be greatly appreciated.
Thanks,
-Simon
February 16, 2008 at 2:25 pm
Why can't you put contact under Supplier and Wholesaler ?
CREATE TABLE Contact (contactID INT NOT NULL,
contact name VARCHAR(100) NOT NULL)
Create Table Supplier (SupplierID INT NOT NULL,
ContactID INT NOT NULL
SupplierName VARCHAR(100) NOT NULL)
SupplierID is the PK and ContactID is the foreign key to Contact. You can also put in a check to make sure the contactid Is not in wholesaler table.
The same thing applies to Wholesaler Table.
my 2 cents.
February 17, 2008 at 1:23 am
You could create SupplierContact and WholesalerContact tables and use one of these at a time to relate a contact to a supplier or to a wholesaler. To have MS SQL enforce the constraint that a contact can link to only a supplier or a wholesaler at any one time you could either
a) Use a trigger to do the checking and rollback if not satisfactory
b) Create a view that will return >1 record in cases where there is a double-up. For example, you could select contacts.* after joining to both supplierContact and wholesaler contact - if both contact types were used then you'd have two duplicate records. Then create a unique index on the view.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply