This is the error I get when I run my "pens table" on SQL+).<br><br>ERROR at line 11:<br>ORA-02270: no matching unique or primary key for this column-list<br><br><br>The following are just 3 samples of my tables (services, treatments, pens).<br>I really don’t understand the error because I already set up a primary key in the treatment table which is the “date_service, pets_pet_id, and services_service_id”<br><br>Thanks.<br><br>CREATE TABLE services (<br>service_id NUMBER(5),<br>type_service VARCHAR2(20) NOT NULL,<br>fee NUMBER(5) NOT NULL,<br> CONSTRAINT services_service_id_pk PRIMARY KEY (service_id));<br>/<br><br>CREATE TABLE treatments (<br>date_service DATE,<br>date_prior_svc DATE,<br>pets_pet_id NUMBER(5),<br>services_service_id NUMBER(5),<br>fee_increment NUMBER(5,0),<br> CONSTRAINT treatments_pets_pet_id_fk FOREIGN KEY(pets_pet_id)<br> references pets(pet_id),<br> CONSTRAINT treatments_services_service_id FOREIGN KEY(services_service_id)<br> references services(service_id),<br> CONSTRAINT treatments_date_service PRIMARY KEY(date_service,pets_pet_id,services_service_id));<br>/<br><br>CREATE TABLE pens(<br>loc_code VARCHAR2(4),<br>equip CHAR(1) NOT NULL,<br>num_days NUMBER(3) NOT NULL,<br>treatments_date_service DATE,<br>treatments_pets_pet_id NUMBER(5),<br>treatments_services_service_id NUMBER (5),<br>CONSTRAINT loc_code_pk PRIMARY KEY (loc_code, treatments_date_service, treatments_pets_pet_id, treatments_services_service_id),<br>CONSTRAINT equip_ck CHECK (equip IN ('y','n')),<br>CONSTRAINT treatments_date_service FOREIGN KEY(treatments_date_service)<br> references treatments(date_service),<br>CONSTRAINT treatments_pets_pet_id FOREIGN KEY(treatments_pets_pet_id)<br> references treatments(pets_pet_id),<br>CONSTRAINT treatments_services_service_id FOREIGN KEY (treatments_services_service_id)<br> references treatments(services_service_id));<br>/<br><br>
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
sql+\oracle 8 table prob
- Thread starter ousmane
- Start date
I think you need to create a compound foreign key in pens (containing treatments_date_service, treatments_pets_pet_id and treatments_services_service_id) rather than attempting to reference each elemement of the parent primary key individually.<br><br>Can't remember the exact syntax (no Oracle server at home) but it's something like<br><FONT FACE=monospace><b><br>CONSTRAINT fk_treatmets FOREIGN KEY (date_service,pets_pet_id,services_service_id)<br> REFERENCES treatments(treatments_date_service, treatments_pets_pet_id, treatments_services_service_id)<br></font></b><br><br>Not a very precise answer this, hope it gives you an idea though.<br> <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href= Cargill's Corporate Web Site</a><br>
Mike - exactly on the mark (as usual!). The alternative is to create unique constraints on columns like treatments.date_service (which is hardly likely to be unique!). <br>Bottom line: Foreign Keys have to reference and match a primary key or unique constraint, column for column.
Wasn't sure I remembered the syntax correctly, really should get Oracle at home.<br><br>Oh - and Ousmane sent me a nice note - thanks Ousmane.<br> <p>Mike<br><a href=mailto:michael.j.lacey@ntlworld.com>michael.j.lacey@ntlworld.com</a><br><a href= Cargill's Corporate Web Site</a><br>
- Status
