Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2017
    Posts
    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,448
    Provided Answers: 8
    3333445555 has to be in the other table first
    hope this help

    See clear as mud


    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 based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,819
    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
  •