CODEWITHSUNDEEP
sqldatabaseoracle-databasedatabase-administration
redCodeAlert
redCodeAlert

Reputation: 653

How to insert data into tables that have foreign keys

SQL> desc invoices
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 INVOICE_ID                                NOT NULL NUMBER(6)
 COMPANY_ID                                         NUMBER(6)
 STUDENT_ID                                         NUMBER(6)
 BILLING_DATE                                       DATE

SQL>

I would like to insert some sample data into this table. The company_id and student_id are foreign keys. This is what I'am entering:

INSERT INTO invoices VALUES (SEQ_INVOICE.NEXTVAL,[what1],[what2],SYSDATE);

I don't know what I am supposed to put in the what1 and what2

SQL> desc companies
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 COMPANY_ID                                NOT NULL NUMBER(6)
 COMPANY_NAME                                       VARCHAR2(30)
 ADDRESS                                            VARCHAR2(128)
 CONTACT_NO                                         VARCHAR2(11)
 NO_OF_EMP                                          NUMBER(10)

SQL>

SQL> desc students
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 STUDENT_ID                                NOT NULL NUMBER(6)
 ST_FNAME                                           VARCHAR2(16)
 ST_SNAME                                           VARCHAR2(16)
 ADDRESS                                            VARCHAR2(128)
 DOB                                                DATE

SQL>

Upvotes: 0

Views: 2160

Answers (3)

Ulphat
Ulphat

Reputation: 782

you have to enter an existed STUDENT_ID from students table and an existed COMPANY_ID from compaies table to invoices. Consider you have data like next

COMPANY_ID     COMPANY_NAME     ADDRESS     CONTACT_NO     NO_OF_EMP
-----------    ------------     --------    ----------     ---------
1              Blah LLC         blah st.    123456         100
2              My Company       My Street   987654321      50

and

STUDENT_ID     ST_FNAME     ST_SNAME     ADDRESS     DOB
-----------    ---------    ---------    --------    ------------
11             Jim          Carrey       ....        1900.25.04
22             Jack         Sparrow      Carrib st.  1700.30.08

then you can use 1 or 2 as COMPANY_ID (in your query [what1]) and 11 or 22 as STUDENT_ID (in your query [what2])

Upvotes: 1

Steven
Steven

Reputation: 49

[what1] is ID of COMPANY_ID [what2] is ID of STUDENT_ID if table Student and Company are empty, you must Insert the record to both tables before Insert

Upvotes: 0

Mackan
Mackan

Reputation: 6271

You need to first enter companies in "companies", and students in "students". Then use those ID's for [what1] and [what2]

Upvotes: 0

Related Questions