I getting an error report of:
Error report - SQL Error: ORA-02270: no matching unique or primary key for this column-list 02270. 00000 - "no matching unique or primary key for this column-list" *Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table. *Action: Find the correct column names using the ALL_CONS_COLUMNS catalog view
Can I know why?
CREATE TABLE STUDENTINFO ( Student_ID VARCHAR2 (10) PRIMARY KEY, Full_Name VARCHAR2 (50) NOT NULL, Contact_Number NUMBER (15)NOT NULL, Address VARCHAR2 (50) NOT NULL, Nationality VARCHAR2 (15) NOT NULL, IC_PassportNo VARCHAR2 (15) NOT NULL, Programme VARCHAR (75) NOT NULL, Email_Address VARCHAR2 (50) NOT NULL REFERENCES USERNAMEPASSWORD(Username), Parents_Number NUMBER (15)NOT NULL, Fingerprint_Template clob );
create table bit_2015_sep_cit4114_fyp_G_ ( Student_ID VARCHAR2 (10) PRIMARY KEY REFERENCES STUDENTINFO(Student_ID), Full_Name VARCHAR2 (50) NOT NULL REFERENCES STUDENTINFO(Full_Name), Nationality VARCHAR2 (15) NOT NULL REFERENCES STUDENTINFO(Nationality), Fingerprint_Template CLOB NOT NULL REFERENCES STUDENTINFO(Fingerprint_Template), "23/10/2015" VARCHAR2 (15) not null, );
I keep nationality and fingerprint as duplicate because it will be time consuming in verifying based on all information stored in studentinfo, therefore by breaking into individual class will be easier and faster. like for the table STUDENTINFO consist 1 million record, in table bit_2015_sep_cit4114_fyp_G_ will only have 40 records. I keep the nationality column because I have 1 more column in my table structure which is visa renewal which calculated based on the value of nationality in the table.