No Unique Index Found

[MikeM1973]

I have to create various tables using queries. The code for the first 2 tables is as follows


CREATE TABLE patient(
SSN char(9) NOT NULL,
PID char(8) NOT NULL,
Address char(60) NOT NULL,
BirthDate DATE NOT NULL,
Gender char(1) NOT NULL,
Name char(20) NOT NULL,
BloodType VARCHAR(2) NOT NULL,
PRIMARY KEY(SSN, PID))

and


CREATE TABLE operation(
OID char(8) NOT NULL,
Name char(20) NOT NULL,
Location char(60) NOT NULL,
Time char(10) NOT NULL,
PRIMARY KEY(OID))

Both of those queries where used to create their tables. My problem comes in when i try to create a relationship table.

CREATE TABLE undergoes(
PID char(8) NOT NULL,
OID char(8) NOT NULL,
FOREIGN KEY(PID) REFERENCES patient(PID),
FOREIGN KEY(OID) REFERENCES operation(OID))


The goal of that table is simply to reference which patient undergoes which surgery, however when i try to run the code i keep getting "No unique index found for the referenced field of the primary table". Any help is greatly appreciated, i have tried googling this problem and i see a lot of people have it but i am not understanding exactly what it means or how i can fix it.

 

[jdraw]

See this for various DDL SQL examples.
http://msdn.microsoft.com/en-us/library/aa140011(office.10).aspx#acfundsql_ddl
There is a sample for CREATE INDEX

 

[MikeM1973]

Alright so i read the link and am attempting to use index's(though we where never told about them in class). I created primary index's for the patient table and the operation table, though i am still getting the same error? I assume there is something i am not understand about index's...

 

[jdraw]

I think the issue is that the PK in patient is compound, in that it consists of 2 fields
PRIMARY KEY(SSN, PID)).

When you try to create the FK relationship from undergoes to patient
FOREIGN KEY(PID) REFERENCES patient(PID),

you can not identify a UNIQUE patient with only part of the PK.

Do you really need SSN as part of the PK in Patient?

 

[MikeM1973]

After speaking with the prof i was told we don't need to use SSN as a primary, apparently a lot of people where having the same problem i was. Thanks for the help though, got everything working after removing ssn as a primary

 

[jdraw]

Glad you got it sorted out.
Good luck.