Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2017
    Posts
    3
    Provided Answers: 1

    Unhappy Unanswered: Insert a foreign key that doesn't exist yet

    hello,
    I just started with the databases, I a, trying to insert a value to a table that contains a foreign key.
    however, that foreign key is still empty from it's source table, all the tables are empty right now :
    create table department(
    dname varchar(20),
    dnumber int primary key not null,
    mgrssn bigint(90),
    mgrstartdate timestamp
    )
    create table employee(
    fname varchar(20),
    minit char,
    lname varchar(20),
    ssn bigint primary key not null,
    bdate timestamp,
    address varchar(50),
    sex char,
    salary numeric(6.6),
    superssn bigint,
    dno int,
    foreign key (superssn) references employee(ssn),
    foreign key (dno) references department(dnumber)
    )

    i am trying to insert the value "dno" that doesn't exist NOT YET
    insert into employee(fname, minit, lname,ssn,bdate,address,sex,salary,superssn, dno)
    values('john','B','smith',123456789, '1965/01/09', '731 Fondren, Houston, tx', 'M', 30000, 333445555, )

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,468
    Provided Answers: 10
    3333445555 has to be in the other table first
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,824
    Provided Answers: 14
    Some RDBMS accept NULL as a foreign key child. You would have to UPDATE the values later, and this is generally regarded as a bad practice.

Tags for this Thread

Posting Permissions

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