One foreign key referenced to two primary keys of different tables?
I've created the database with table address having owner_id which is referencing to two primary keys of different table but gives error error "cannot update a child row: foreign ke constraint failed"
This is the structure of my table
CREATE TABLE Address
(
OwnerID VARCHAR(5) NOT NULL,
Line1 VARCHAR(40),
City VARCHAR(40),
Postcode VARCHAR(4),
AddressType INT,
PRIMARY KEY (OwnerID, AddressType),
FOREIGN KEY (AddressType) REFERENCES AddressType(AddressType),
FOREIGN KEY (OwnerID) REFERENCES Supplier(SupplierID),
FOREIGN KEY (OwnerID) REFERENCES Customer(CustomerID)
);
Can anyone solve? or atleast give me an alternative?
Your data structure does not quite make sense. If you have an address, how do you know what the owner is? A supplier or a customer? One way to fix this is:
OwnerOrCustomer char(1) not null, /* has values O or C */
OwnerId int,
CustomerId int,
That is, store the values in separate fields.
A more sophisticated way of solving this problem is to have a new concept called "entity" or "organization" or something like that. This would supply an id for both suppliers and customers (and, who knows, partners and prospects and competitors as well). The id for suppliers an customers would really be an EntityId , distinct between them.
I should add that such a data structure is rather rare. In most companies, the customer databases are rather separate from the supplier databases, and there is no single consolidated address table. You might consider this another alternative, particularly if suppliers tend to be companies and customers tend to be individuals. The addresses for an organization has different characteristics from an address for an individual.
链接地址: http://www.djcxy.com/p/65886.html下一篇: 一个外键引用了不同表的两个主键？