Primary Key Constraint

[Prosdo]

Hey guys. This is a school related question. My lab says the following:

1. Add a new Vendor table
a. See table above for fields and data types
b. Use your username as the table definition, i.e., userid.Vendor, instead of Demo
c. Add Primary key ProductNo as a Constraint

Okay, I created the table with the fields and data types. Now when I go to add the primary key with the constraint and I get
ERROR at line 2:
ORA-00904: "VENDOR_NO": invalid identifier

When I enter:

ALTER TABLE slp5065.vendor ADD
                              CONSTRAINT ProductNo PRIMARY KEY (Vendor_No);
 

This is the command the teacher listed on the paper:

Alter tables:
    Adding a single field as a Primary Key 
        ALTER TABLE tablename ADD
                              CONSTRAINT pk_name PRIMARY KEY (column_name);

What am I doing wrong?

[mwasif]

Moved to Oracle Forum.
Checkout this post.

[amitpatel66]

Hi,

Please make use of code tags when ever you post aqny code in this forum

Thanks
MODERATOR

[amitpatel66]

Hey guys. This is a school related question. My lab says the following:

1. Add a new Vendor table
a. See table above for fields and data types
b. Use your username as the table definition, i.e., userid.Vendor, instead of Demo
c. Add Primary key ProductNo as a Constraint

Okay, I created the table with the fields and data types. Now when I go to add the primary key with the constraint and I get
ERROR at line 2:
ORA-00904: "VENDOR_NO": invalid identifier

When I enter:

ALTER TABLE slp5065.vendor ADD
                              CONSTRAINT ProductNo PRIMARY KEY (Vendor_No);
 

This is the command the teacher listed on the paper:

Alter tables:
    Adding a single field as a Primary Key 
        ALTER TABLE tablename ADD
                              CONSTRAINT pk_name PRIMARY KEY (column_name);

What am I doing wrong?

Please post your table structure for reference?

[Prosdo]

Please post your table structure for reference?

Sorry about posting it in the wrong forum. Here is my table setup:

[Prosdo]

I see my dumb error on that one. But I am having a problem with the foreign key. My paper says:

3. Alter Vendor table to add constraint of Vendor.Zip_Code as a Foreign Key field references Zipcode.Zip_code field.

ALTER TABLE tablename ADD
                              CONSTRAINT fk_name FOREIGN KEY (column_name)
                              REFERENCES tablename (column_name);

I typed in:

ALTER TABLE slp5065.vendor ADD
                              CONSTRAINT slp5065.vendor.zip_code FOREIGN KEY (vendor_zip_code)
                              REFERENCES slp5065.zipcode.zip_code (zip_code);

I got the error:
CONSTRAINT slp5065.vendor.zip_code FOREIGN KEY (vendor_zip_code)
*

ERROR at line 2:
ORA-00904: : invalid identifier

Here are my tables:

[Prosdo]

Can anyone help? I have to turn it in by midnight est. Which is in 38 minutes. I e-mailed my teacher about this a while ago and he hasn't gotten back to me.

[amitpatel66]

I see my dumb error on that one. But I am having a problem with the foreign key. My paper says:

3. Alter Vendor table to add constraint of Vendor.Zip_Code as a Foreign Key field references Zipcode.Zip_code field.

ALTER TABLE tablename ADD
                              CONSTRAINT fk_name FOREIGN KEY (column_name)
                              REFERENCES tablename (column_name);

I typed in:

ALTER TABLE slp5065.vendor ADD
                              CONSTRAINT slp5065.vendor.zip_code FOREIGN KEY (vendor_zip_code)
                              REFERENCES slp5065.zipcode.zip_code (zip_code);

I got the error:
CONSTRAINT slp5065.vendor.zip_code FOREIGN KEY (vendor_zip_code)
*

ERROR at line 2:
ORA-00904: : invalid identifier

Here are my tables:

Please let me know the following:

What is slp5065?? a schema name?
What is Zipcode?? a table name??
And I dont find your table structure.Please POST your table structure.

[Prosdo]

Please let me know the following:

What is slp5065?? a schema name?
What is Zipcode?? a table name??
And I dont find your table structure.Please POST your table structure.

Sorry very new to Oracle and all. slp5065 is my user name. When we did other projects for the class they were named demo.customer. So for each student we had to just put our userid.tablename. slp5065.Zipcode is a table. I guess this is the table structure. If it isn't please let me know and I will try to find out what it is.

[rpnew]

I see my dumb error on that one. But I am having a problem with the foreign key. My paper says:

3. Alter Vendor table to add constraint of Vendor.Zip_Code as a Foreign Key field references Zipcode.Zip_code field.

ALTER TABLE tablename ADD
                              CONSTRAINT fk_name FOREIGN KEY (column_name)
                              REFERENCES tablename (column_name);

I typed in:

ALTER TABLE slp5065.vendor ADD
                              CONSTRAINT slp5065.vendor.zip_code FOREIGN KEY (vendor_zip_code)
                              REFERENCES slp5065.zipcode.zip_code (zip_code);

I got the error:
CONSTRAINT slp5065.vendor.zip_code FOREIGN KEY (vendor_zip_code)
*

ERROR at line 2:
ORA-00904: : invalid identifier

Here are my tables:

hi,

First tell me in your query what is following......

ALTER TABLE slp5065.vendor ADD
                              CONSTRAINT slp5065.vendor.zip_code FOREIGN KEY (vendor_zip_code)
                              REFERENCES slp5065.zipcode.zip_code (zip_code);

It should be tablename contains the column you are referencing as foreign key...

From your code it seems that you have two tables named slp5065.vendor and slp5065.zipcode and if i'm not wrong change your query to this..

ALTER TABLE slp5065.vendor ADD
                              CONSTRAINT slp5065.vendor.zip_code FOREIGN KEY (vendor_zip_code)
                              REFERENCES slp5065.zipcode(zip_code);
 

As per my knwoledge...... If i'm wrong somewhere let me know........

Regards,
RP

[Prosdo]

Thank you for the reply. I tried what you said and I got the following error:

CONSTRAINT slp5065.vendor.zip_code FOREIGN KEY (vendor_zip_code)
*

ERROR at line 2:
ORA-00904: : invalid identifier

[amitpatel66]

Thank you for the reply. I tried what you said and I got the following error:

CONSTRAINT slp5065.vendor.zip_code FOREIGN KEY (vendor_zip_code)
*

ERROR at line 2:
ORA-00904: : invalid identifier

Do the following and paste the output here:

 
SQL> DESC <table_name>
 
 

In the above code replace table_name with your table name and paste the output here for my reference

[Prosdo]

slp5065.vendor table
Name Null? Type
VENDOR_NO NOT NULL NUMBER(6)
VENDOR_NAME VARCHAR2(30)
VENDOR_ADDRESS VARCHAR2(35)
VENDOR_ZIP_CODE NOT NULL NUMBER(38)

slp5065.zipcode table
Name Null? Type
ZIP_CODE NOT NULL NUMBER(5)
ZIP_CITY VARCHAR2(15)
ZIP_STATE VARCHAR2(2)

[amitpatel66]

slp5065.vendor table
Name Null? Type
VENDOR_NO NOT NULL NUMBER(6)
VENDOR_NAME VARCHAR2(30)
VENDOR_ADDRESS VARCHAR2(35)
VENDOR_ZIP_CODE NOT NULL NUMBER(38)

slp5065.zipcode table
Name Null? Type
ZIP_CODE NOT NULL NUMBER(5)
ZIP_CITY VARCHAR2(15)
ZIP_STATE VARCHAR2(2)

Try this:

 
ALTER TABLE vendor_table ADD FOREIGN KEY (vendor_zip_code) REFERENCES zipcode(zip_code);