×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

sql+\oracle 8 table prob

sql+\oracle 8 table prob

sql+\oracle 8 table prob

(OP)
This is the error I get when I run my "pens table" on SQL+).

ERROR at line 11:
ORA-02270: no matching unique or primary key for this column-list


The following are just 3 samples of my tables (services, treatments, pens).
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”

Thanks.

CREATE TABLE services (
service_id        NUMBER(5),
type_service    VARCHAR2(20) NOT NULL,
fee            NUMBER(5) NOT NULL,
 CONSTRAINT services_service_id_pk PRIMARY KEY (service_id));
/

CREATE TABLE treatments (
date_service         DATE,
date_prior_svc       DATE,
pets_pet_id          NUMBER(5),
services_service_id  NUMBER(5),
fee_increment        NUMBER(5,0),
 CONSTRAINT treatments_pets_pet_id_fk FOREIGN KEY(pets_pet_id)
   references pets(pet_id),
 CONSTRAINT treatments_services_service_id FOREIGN KEY(services_service_id)
   references services(service_id),
 CONSTRAINT treatments_date_service PRIMARY KEY(date_service,pets_pet_id,services_service_id));
/

CREATE TABLE pens(
loc_code                       VARCHAR2(4),
equip                          CHAR(1) NOT NULL,
num_days                       NUMBER(3) NOT NULL,
treatments_date_service        DATE,
treatments_pets_pet_id         NUMBER(5),
treatments_services_service_id NUMBER (5),
CONSTRAINT loc_code_pk PRIMARY KEY (loc_code, treatments_date_service, treatments_pets_pet_id, treatments_services_service_id),
CONSTRAINT equip_ck CHECK (equip IN ('y','n')),
CONSTRAINT treatments_date_service FOREIGN KEY(treatments_date_service)
  references treatments(date_service),
CONSTRAINT treatments_pets_pet_id FOREIGN KEY(treatments_pets_pet_id)
  references treatments(pets_pet_id),
CONSTRAINT  treatments_services_service_id FOREIGN KEY (treatments_services_service_id)
  references treatments(services_service_id));
/

RE: sql+\oracle 8 table prob

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.

Can't remember the exact syntax (no Oracle server at home) but it's something like

CONSTRAINT fk_treatmets FOREIGN KEY (date_service,pets_pet_id,services_service_id)
  REFERENCES treatments(treatments_date_service, treatments_pets_pet_id, treatments_services_service_id)


Not a very precise answer this, hope it gives you an idea though.

Mike
michael.j.lacey@ntlworld.com
Cargill's Corporate Web Site

RE: sql+\oracle 8 table prob

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!).  
Bottom line: Foreign Keys have to reference and match a primary key or unique constraint, column for column.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close