CODEWITHSUNDEEP
sqldatabaseoracle-database
Rozen
Rozen

Reputation: 139

A child table reference parent table which has FK from a grandparent table?

So I have something like a table call People, then I use an isA relationship to branch out into Professor and Student. Of course I have fk of student and prof references to People. Now I want to make another isA relationship which branched out from Student, 2 tables, call non-GTA and GTA. Then my fk in these 2 going to reference to People or Student? I mean can i reference a FK to a FK?

Here is my attempt

create table People
(name varchar(50),
ssn varchar(50) not null,
G# varchar(50),
primary key (ssn),
unique(name, ssn, G#))

create table Professor
(
name varchar(50),
ssn varchar(50) not null,
G# varchar(50),
teach_record varchar(50),
primary key (ssn),
unique (ssn, G#),
foreign key (name, ssn, G#) references People(name, ssn, G#) ON DELETE CASCADE
)

create table Student
(
name varchar(50),
ssn varchar(50) not null,
G# varchar(50),
primary key (ssn),
unique (ssn, G#),
foreign key (name, ssn, G#) references People(name, ssn, G#) ON DELETE CASCADE
)

This is the non GTA

create table NonGTA
(
name varchar(50),
ssn varchar(50) not null,
G# varchar(50),
primary key (ssn),
unique (ssn, G#),
foreign key (name, ssn, G#) references Student(name, ssn, G#) ON DELETE CASCADE
)

Upvotes: 0

Views: 549

Answers (1)

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95732

If I understand you, your GTA and NonGTA tables should reference the student table. I understand that GTA and NonGTA have something to do with students, and they have nothing to do with professors or any other kind of person.

But a foreign key has to reference a set of columns that is declared to be either PRIMARY KEY or UNIQUE. The foreign key you're trying to declare in NonGTA can't reference {name, ssn, G#}, because there's no suitable constraint on that set of columns. Reference {ssn} or {ssn, G#} instead.

Other possible issues

If G# is supposed to be unique, then People.G# should be declared UNIQUE. It might need to be declared NOT NULL, too.

Think twice about including extra, unnecessary columns in your UNIQUE constraints. There's probably no good reason to include "name" in a UNIQUE constraint. (Especially since you can't easily correct misspellings--Oracle doesn't support ON UPDATE CASCADE.)

Upvotes: 2

Related Questions