Here's the follow-up to the problem.
I did exactly what you told me, and it's working fine. But my problem is that I am creating a new table, proj_ven_data. I've done same for a table proj_team_data:
/* project_team_data*/
sql> create table proj_team_data(proj_id number(8),
emp_id number(8),
constraint p1_pkey primary key(proj_id,ven_id),
constraint fe1_fkey foreign key(proj_id) references
project_data(sid),
constraint fe2_fkey foreign key(emp_id) references
employee_data(sid));
/*project table*/
create table project_data
( sid number(8),
user_id varchar2(20),
name varchar(60),
proj_type varchar2(1),
proj_ld number(8),
opbal_con number,
opbal_non number,
cubal_con number,
cubal_non number,
pro_stat varchar2(1),
constraint proj_pkey primary key(sid),
constraint proj_fkey foreign key(proj_ld) references
employee_data(sid)
);
/* employee table*/
create table employee_data
( sid number(8),
name varchar2(40),
desi_id number(8),
email varchar2(60),
passwd varchar2(40),
grp_id number(8),
constraint emp_pkey primary key(sid)
);
/* vendor table*/
create table vendor_data
( sid number(8),
name varchar2(60),
address varchar2(150),
city varchar2(25),
state varchar2(25),
pin varchar2(6),
country varchar2(20),
fax varchar2(20),
phone varchar2(20),
email varchar2(60),
constraint vend_pkey primary key(sid)
);
SQL> create table proj_ven_data(
2 proj_id number(8),
3 ven_id number(8),
4 constraint pv_pkey primary key(proj_id,ven_id),
5 constraint f1_fkey foreign key(proj_id) references
project_data(sid),
6 constraint f2_fkey foreign key(ven_id) references
vendor_data(sid));
Table created.
SQL> commit;
Commit complete.
SQL> desc proj_ven_data;
Name Null? Type
------------------------------- -------- ----
PROJ_ID NOT NULL NUMBER(8)
VEN_ID NOT NULL NUMBER(8)
SQL> select * from proj_ven_data;
no rows selected
What's the problem? Please help me out.
Requires Free Membership to View
Thanks for doing the test. From this test, we've verified that you can create a table, insert data into it, and get that data back out. So things are working as they are supposed to. Now we need to take a look at what you are doing and see why you don't have any data in your table.
> /* project_team_data*/ > > sql> create table proj_team_data(proj_id number(8), > emp_id number(8), > constraint p1_pkey primary key(proj_id,ven_id), > constraint fe1_fkey foreign key(proj_id) references > project_data(sid), > constraint fe2_fkey foreign key(emp_id) > references > employee_data(sid)); > > /*project table*/ > create table project_data > ( sid number(8), > user_id varchar2(20), > name varchar(60), > proj_type varchar2(1), > proj_ld number(8), > opbal_con number, > opbal_non number, > cubal_con number, > cubal_non number, > pro_stat varchar2(1), > constraint proj_pkey primary key(sid), > constraint proj_fkey foreign key(proj_ld) > references > employee_data(sid) > ); > > /* employee table*/ > create table employee_data > ( sid number(8), > name varchar2(40), > desi_id number(8), > email varchar2(60), > passwd varchar2(40), > grp_id number(8), > constraint emp_pkey primary key(sid) > ); > > /* vendor table*/ > > create table vendor_data > ( sid number(8), > name varchar2(60), > address varchar2(150), > city varchar2(25), > state varchar2(25), > pin varchar2(6), > country varchar2(20), > fax varchar2(20), > phone varchar2(20), > email varchar2(60), > constraint vend_pkey primary key(sid) > ); > > SQL> create table proj_ven_data( > 2 proj_id number(8), > 3 ven_id number(8), > 4 constraint pv_pkey primary > key(proj_id,ven_id), > 5 constraint f1_fkey foreign key(proj_id) > references > project_data(sid), > 6 constraint f2_fkey foreign key(ven_id) > references > vendor_data(sid)); > > Table created.So here your table has been created. You've gotten a response that the table was created. So far, so good.
> SQL> commit; > > Commit complete. > > SQL> desc proj_ven_data; > Name Null? Type > ------------------------------- -------- ---- > PROJ_ID NOT NULL NUMBER(8) > VEN_ID NOT NULL NUMBER(8)The DESCRIBE command verifies that the table is indeed created with the two columns you specified.
> SQL> select * from proj_ven_data; > > no rows selectedThere are no rows in your table because you have yet to put them into the table. Until you perform an INSERT command on this table, no rows will be in this table.
It is obvious from your CREATE TABLE statement that the PROD_ID column refers to the PROJECT_DATA table and the VEN_ID column refers to the VENDOR_DATA table. Did you assume that the database would populate the PROJ_VEN_DATA table with data from these two parent tables? This does not happen automatically in any relational database. How is the system to know which project ID relates to which vendor ID? It can't. You have to supply this information. All that the Foreign Key constraint does is to ensure that the vendor ID exists in the VENDOR_DATA table before you can insert it into the PROJ_VEN_DATA table, and similarly for the project ID.
The simple reason no data is returned is because you have not inserted data into the table! Once you do that, your query will return rows from that table.
This was first published in April 2002
Join the conversationComment
Share
Comments
Results
Contribute to the conversation