Relationsip question using UNION clause in a View...

[Murphy]

The scenario is as follows:
1) 3 suppliers provide seperate pricelists that are impoted in the db
2) These pricelists are joined into a single view using a union statements
called tmpSuppliers
3) The view from step 2 is joined to the tmpStock table and displayed in a
view called tmpProducts

We are able to add new rows to the tmpStock table via the tmpProducts view
and when the row is written the appropriate cost price etc is displayed,
however we are unable to delete rows from this view with an error being
returned:
"Insufficient key column information for updating or refreshing"

When the tmpProducts view is displayed in an MS Access no editing is
possible at all (add, edit or delete)

Any help would be greatly appreciated...


Murphy


Create Table tmpSupplierA
(SupplierCode Char(3),
Product VarChar(25),
Cost Money)

Create Table tmpSupplierB
(SupplierCode Char(3),
Product VarChar(25),
Cost Money)

Create Table tmpSupplierC
(SupplierCode Char(3),
Product VarChar(25),
Cost Money)

Create Table tmpStock
(StockID INT IDENTITY,
Supplier Char(1),
SupplierCode Char(3),
Price Money)

GO

INSERT INTO tmpSupplierA Values ('MSE','Mouse', 10)
INSERT INTO tmpSupplierB Values ('KYB', 'Keyboard', 15)
INSERT INTO tmpSupplierC Values ('MON', 'Monitor', 100)
INSERT INTO tmpSupplierC Values ('KYB', 'Keyboard', 14)

INSERT INTO tmpStock (Supplier, SupplierCode, Price) Values ('A', 'MSE', 15)
INSERT INTO tmpStock (Supplier, SupplierCode, Price) Values ('B', 'KYB', 20)
INSERT INTO tmpStock (Supplier, SupplierCode, Price) Values ('C', 'MON',
110)

GO

CREATE VIEW tmpSuppliers
AS
SELECT 'A' As Supplier, SupplierCode, Product, Cost From tmpSupplierA
UNION
SELECT 'B'As Supplier, SupplierCode, Product, Cost From tmpSupplierB
UNION
SELECT 'C'As Supplier, SupplierCode, Product, Cost From tmpSupplierC

GO

CREATE VIEW tmpProducts
AS
SELECT tmpStock.StockID, tmpStock.Supplier, tmpStock.SupplierCode,
tmpSuppliers.Product, tmpSuppliers.Cost, tmpStock.Price
FROM tmpStock INNER JOIN tmpSuppliers ON
tmpStock.Supplier=tmpSuppliers.Supplier AND
tmpStock.SupplierCode=tmpSuppliers.SupplierCode

GO

 

[J. M. De Moor]

Murphy

I guess I have a more fundamental question. Why are there 3 separate
tmpSupplier tables. Aren't you making things difficult for yourself this
way? (I may understand your columns wrong...if so, sorry.) Also, is the
price different for the same item if it comes from different suppliers?

CREATE TABLE Products (
supplier_code CHAR(3) NOT NULL PRIMARY KEY,
product VARCHAR(25) NOT NULL,
price NUMBER(7, 2) NOT NULL
);

INSERT INTO Products VALUES ('KYB', 'Keyboard', 20.00);
INSERT INTO Products VALUES ('MON', 'Monitor', 110.00);
INSERT INTO Products VALUES ('MSE', 'Mouse', 15.00);

CREATE TABLE Costs (
supplier CHAR(1) NOT NULL,
supplier_code CHAR(3) NOT NULL
REFERENCES Products(supplier_code),
cost NUMBER(7, 2) NOT NULL
PRIMARY KEY(supplier, supplier_code)
);

INSERT INTO Costs VALUES ('A', 'MSE', 10.00);
INSERT INTO Costs VALUES ('B', 'KYB', 15.00);
INSERT INTO Costs VALUES ('C', 'MON', 100.00);
INSERT INTO Costs VALUES ('C', 'KYB', 14.00);

Then your view would look something like this:

CREATE VIEW ExtProducts AS
SELECT S.supplier, S.supplier_code, P.product, P.price, C.cost
FROM Costs AS C
JOIN Products AS P ON P.supplier_code = C.supplier_code;

HTH,
Joe De Moor

 

[Murphy]

Thanks Joe, after battling with this for ages I merged the three tables into
one and set the Primary Key as the combination of supplier and suppliercode.

Thanks

Henry