Primary/Foreign Key Conflict.?

Question

I have two tables: SUPPLIERS, PART.

The primary key in table SUPPLIERS is SNO. It also has a column named CITY.

The PART table also has the CITY column. However, in neither tables is CITY a primary key. How do I make the PART table reference the SUPPLIERS table? I did the following and got an error.

CREATE TABLE SUPPLIERS
(SNO VARCHAR(3),
SNAME VARCHAR(6),
STATUS NUMBER(3),
CITY VARCHAR (5),
CONSTRAINT SUPPLIERS_SNO_pk PRIMARY KEY (SNO));

CREATE TABLE PART
(PNO VARCHAR(3),
PNAME VARCHAR(6),
COLOR VARCHAR(6),
WEIGHT NUMBER(3),
CITY VARCHAR(5),
CONSTRAINT PART_PNO_pk PRIMARY KEY(PNO),
CONSTRAINT PART_CITY_fk FOREIGN KEY (CITY) REFERENCES SUPPLIERS(CITY));

ORA-02270: no matching unique or primary key for this column-list

I know that CITY is not the primary key in SUPPLIERS table but I have manipulated both tables using composite keys but to no avail! Please tell me how I can write this so it will work.

Thanks

Answer 1

1. Table suppliers MUST have UNIQUE index on CITY column.

2. For Parts table add constraint as

Create table .....

... CONSTRAINT "MYFK" FOREIGN KEY("CITY")

REFERENCES SUPPLIERS ("CITY")) ...



This would make a problems if you have multiple suppliers from the same city, so add 3rd table 'cities' with name and id, use id for primary key and reference this as your FK in both tables

Answer 2

seems to me the only relationship between the parts and suppliers tables would be the supplier number... so for every part there would be a supplier associated with it (sno?). and for every sno there would be one or many part numbers. I surely wouldn't make city a foreign key, since it would seem to me that type of relationship could be more easily evolved from a query.

Answer 3

Add SNO as a field to the PART table.

This is not a key field. It just contains the key of SUPPLIERS.



Have fun.