user2991463 user2991463 - 1 month ago 6
SQL Question

Invalid identifier but I don't want to add other foreign keys to the tables but it's not letting me

Getting an error at the bolded. What am I doing wrong? I don't want to add the other primary keys into the table...so how do I get this thing working? I need for the tables to reference DonType only, not the other foreign keys within the table donor.

CREATE TABLE PROGRAMS
(PROGNAME NVARCHAR2 (30) NOT NULL,
PRIMARY KEY (PROGNAME));

CREATE TABLE CAMPUS
(CAMPNAME NVARCHAR2 (6) NOT NULL,
Q1 NUMBER (10, 2),
Q2 NUMBER (10, 2),
Q3 NUMBER (10, 2),
Q4 NUMBER (10, 2),
PRIMARY KEY (CAMPNAME));

CREATE TABLE LOCATED_AT
(PROGNAME NVARCHAR2 (30) NOT NULL,
CAMPNAME NVARCHAR2 (15) NOT NULL,
PRIMARY KEY (PROGNAME, CAMPNAME),
FOREIGN KEY (PROGNAME)
REFERENCES PROGRAMS
ON DELETE CASCADE,
FOREIGN KEY (CAMPNAME)
REFERENCES CAMPUS
ON DELETE CASCADE);

CREATE TABLE DONOR
(DONNAME NVARCHAR2 (22) NOT NULL,
DONTYPE NVARCHAR2 (1) NOT NULL,
PROJECT NVARCHAR2 (15),
PRIMARY KEY (DONNAME, DONTYPE));

CREATE TABLE COMMITTEE_MEMBER
(CMNAME NVARCHAR2 (20) NOT NULL,
DONTYPE NVARCHAR2 (1) NOT NULL,
Q1 NUMBER (10, 2),
Q2 NUMBER (10, 2),
Q3 NUMBER (10, 2),
Q4 NUMBER (10, 2),
PRIMARY KEY (CMNAME, DONTYPE),
FOREIGN KEY (DONTYPE)
**REFERENCES DONOR**
ON DELETE CASCADE);

CREATE TABLE DONATION_TYPE
(DONTYPE NVARCHAR2 (1) NOT NULL,
Q1 NUMBER (10, 2),
Q2 NUMBER (10, 2),
Q3 NUMBER (10, 2),
Q4 NUMBER (10, 2),
PRIMARY KEY (DONTYPE),
FOREIGN KEY (DONTYPE)
REFERENCES DONOR
ON DELETE CASCADE);

CREATE TABLE CONTRIBUTION
(CMNAME NVARCHAR2 (22) NOT NULL,
PROGNAME NVARCHAR2 (30) NOT NULL,
DONNAME NVARCHAR2 (22) NOT NULL,
AMOUNT NUMERIC (10, 2) NOT NULL,
DATE_ NVARCHAR2 (6) NOT NULL,
PRIMARY KEY (CMNAME, PROGNAME, DONNAME, AMOUNT, DATE_),
FOREIGN KEY (CMNAME, PROGNAME)
REFERENCES COMMITTEE_MEMBER
ON DELETE CASCADE,
FOREIGN KEY (PROGNAME)
REFERENCES PROGRAMS
ON DELETE CASCADE,
FOREIGN KEY (DONTYPE)
REFERENCES DONOR
ON DELETE CASCADE);

Answer

If you want to reference only one column you can do it as the following:

FOREIGN KEY (DONTYPE)
REFERENCES DONOR(DONTYPE)

But you can only reference columns that contain unique values. So you have to change your primary key to only one column or add an UNIQUE constraint to your referenced table(column) with the following command:

CREATE TABLE DONOR
(DONNAME NVARCHAR2 (22) NOT NULL,
DONTYPE NVARCHAR2 (1) NOT NULL,
PROJECT NVARCHAR2 (15),
PRIMARY KEY (DONNAME, DONTYPE),
CONSTRAINT dontype_unique UNIQUE (DONTYPE));
Comments