I have these tables:
CREATE TABLE items(
id int,
...
)
CREATE TABLE types(
id char(1),
...
)
CREATE TABLE prices(
item int,
type char(1) NULL, --can not be null because it is in PK!
price decimal,
PRIMARY KEY (item, version),
FOREIGN KEY (item) REFERENCES items(id),
FOREIGN KEY (type) REFERENCES types(id)
)
Not all items have different price for type:
INSERT INTO prices (item, type, price)
VALUES (1,'A',10.0),
VALUES (1,'B',20.0),
VALUES (1,'C',20.0),
VALUES (2,NULL,50.0),
VALUES (3,'A',10.0),
VALUES (3,'B',20.0),
VALUES (4,NULL,70.0);
As you can se some items (2,4) have just one price. This way I can set the foreign key between prices and types but I can not add type in the price's primary key because it is nullable... how to solve this problem? I need both foreign key and primary key to type field, but this can be optional.
you could create a "surrogate" primary key on the prices table based on a identidy so you dont have to worry about mainting the pk column. You can then create a unique index across your item and type columns.
the id column which is your pk then just exists so mqsql can keep the rows organised on the file system.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments