Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2008
    Posts
    19

    Quick foreign key question..

    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..

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2008
    Posts
    19
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •