Quick foreign key question..

makko187 · #1 (**permalink**) 07-20-09, 17:43

Hi,

I am studying for an upcoming exam and came across this question and it has me puzzled.

Code:

State two conditions about the attributes Employees.deptNum and Departments.deptNum – that must be satisfied in order for an RDBMS to accept Employees.deptNum as a foreign-key.

Does the question mean two conditions of the data entered or conditions of the attribute like type, length etc..?

First Case:

Code:

Both attributes must be of the same type and both tables (Employees and Departments) must be in the same database.

Second Case:

Code:

The data entered must be either null or else match one of the values in Employees.deptNum to be accepted as a foreign key..

Im not really sure how to answer this tbh.

How would you guys answer this?

Thanks..

pootle flump · #2 (**permalink**) 07-21-09, 04:07

I don't believe this is to do with the data.

There is a condition that must be true for one of the attributes you mention.

healdem · #3 (**permalink**) 07-21-09, 04:57

second case is certainly true
for a foreign constraint the value must exist in the table referred to.
ie employees.deptID must exist in departments
however the second part (ie or null) depends on the definition of the column in the child table (ie if employee.deptID is allowed as nullable) then the second case rings true

the first case is more abstract, it could be true however Im pretty certain Ive come across a foreign key which didnt' use the same datatype (however the datatype could be coerced to the foreign key column/parent table column type (IIRC it was upsizing from an integer to a Big Integer) it wouldnt' work with decimals....

Generally I would expect a SQL engine to enforce foreign key constraints within the same physical database. although it may be possible in say MySQL to enforce a constraint against a different db.. but I've never had the need to try it so i dunno if it would work.

makko187 · #4 (**permalink**) 07-21-09, 12:00

The question after that was about the data entered so i'm pretty sure this question is talking about the attributes condition.

@pootle flump: What condition must be true?
is it: Department.DeptNum must be a primary key?

second condition?could we say same type to get full marks for the question even though technically this is not the case?

Thanks for your help guys.

r937 · #5 (**permalink**) 07-21-09, 12:16

Quote:

Originally Posted by makko187

is it: Department.DeptNum must be a primary key?

you're getting very warm, but you're not quite there yet

pootle flump · #6 (**permalink**) 07-21-09, 12:22

Quote:

Originally Posted by makko187

second condition?could we say same type to get full marks for the question even though technically this is not the case?

It's more a case of the physical implementation and whether or not automatic coercion is supported. TMK there is no requirement in the relational model - I admit it is too long since my school days to remember whether or not it explicitly forbids it.
I would suggest this is not really something to put into an academic paper.

Internet Services

Web Development

Digital Marketing

Consumer Tech