Ask the Expert

Problem creating rows, then not being able to view them, part 2

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 selected
There 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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: