Home » SQL & PL/SQL » SQL & PL/SQL » SQL-PLUS FOREIGN KEY HELP!! (oracle sql plus 9.2.0.1.0)
SQL-PLUS FOREIGN KEY HELP!! [message #362896] Thu, 04 December 2008 07:37 Go to next message
roberthurley14
Messages: 13
Registered: December 2008
Junior Member
REALLY need help here guys! my foreign keys wont recognise my inputs, here's the code i have (please help asap, im a noob..i know Razz):

CREATE SEQUENCE STAFFID START WITH 001;


CREATE TABLE CUSTOMER (
USERID VARCHAR2(80) NOT NULL,
FIRSTNAME VARCHAR2(80) NOT NULL,
LASTNAME VARCHAR2(80) NOT NULL,
ADDR1 VARCHAR2(80) NOT NULL,
ADDR2 VARCHAR2(40),
CITY VARCHAR2(80) NOT NULL,
STREET VARCHAR2(80) NOT NULL,
POSTCODE VARCHAR2(20) NOT NULL,
COUNTRY VARCHAR2(20) NOT NULL,
PHONE VARCHAR2(80) NOT NULL,
CONSTRAINT PK_CUSTOMER PRIMARY KEY (USERID)
);

ALTER TABLE CUSTOMER
ADD EMAIL VARCHAR(80);


CREATE TABLE TOY (
TOYID VARCHAR2(10) NOT NULL,
TOYNAME VARCHAR2(80) NOT NULL,
DESCRIPTION VARCHAR2(255) NOT NULL,
CATEGORY VARCHAR(80) NOT NULL,
AGERANGE INTEGER,
CONSTRAINT PK_TOY PRIMARY KEY (TOYID),
FOREIGN KEY (STAFFID) REFERENCES STAFF(STAFFID));








CREATE TABLE STAFF (
STAFFID VARCHAR2(20) NOT NULL,
STAFFNAME VARCHAR2(80) NOT NULL,
STAFFADDR1 VARCHAR2(255) NOT NULL,
STAFFADDR2 VARCHAR2(255) NOT NULL,
STAFFADDR3 VARCHAR2(255) NOT NULL,
SALARY INTEGER,
STAFFSECTION VARCHAR2(30) NOT NULL,
CONSTRAINT PK_STAFF PRIMARY KEY (STAFFID),
FOREIGN KEY (RECEIPTID) REFERENCES RECEIPT(RECEIPTID));

CHECK (STAFFID Between 0 and 999));



CREATE TABLE RECEIPT (
RECEIPTID VARCHAR2(80) NOT NULL,
DATEOFISSUE VARCHAR2(80) NOT NULL,
PURCHASES VARCHAR2(200) NOT NULL,
TOTALCOST INTEGER ,
CONSTRAINT PK_RECEIPT PRIMARY KEY (RECEIPTID),
FOREIGN KEY (DISCOUNTID) REFERENCES DISCOUNT(DISCOUNTID));


CREATE TABLE DISCOUNT (
DISCOUNTID VARCHAR2(20) NOT NULL,
DISCOUNTTYPE VARCHAR2(20) NOT NULL,
AMOUNTOFDISCOUNT INTEGER NOT NULL,
CONSTRAINT PK_DISCOUNT PRIMARY KEY (DISCOUNTID),
FOREIGN KEY (USERID) REFERENCES CUSTOMER(USERID) );

this is the error it happens on ALL tables:

SQL> CREATE TABLE CUSTOMER (
2 USERID VARCHAR2(80) NOT NULL,
3 FIRSTNAME VARCHAR2(80) NOT NULL,
4 LASTNAME VARCHAR2(80) NOT NULL,
5 ADDR1 VARCHAR2(80) NOT NULL,
6 ADDR2 VARCHAR2(40),
7 CITY VARCHAR2(80) NOT NULL,
8 STREET VARCHAR2(80) NOT NULL,
9 POSTCODE VARCHAR2(20) NOT NULL,
10 COUNTRY VARCHAR2(20) NOT NULL,
11 PHONE VARCHAR2(80) NOT NULL,
12 CONSTRAINT PK_CUSTOMER PRIMARY KEY (USERID)
13 );

Table created.

SQL> CREATE TABLE DISCOUNT (
2 DISCOUNTID VARCHAR2(20) NOT NULL,
3 DISCOUNTTYPE VARCHAR2(20) NOT NULL,
4 AMOUNTOFDISCOUNT INTEGER NOT NULL,
5 CONSTRAINT PK_DISCOUNT PRIMARY KEY (DISCOUNTID),
6 FOREIGN KEY (USERID) REFERENCES CUSTOMER(USERID) );
FOREIGN KEY (USERID) REFERENCES CUSTOMER(USERID) )
*
ERROR at line 6:
ORA-00904: "USERID": invalid identifier

Re: SQL-PLUS FOREIGN KEY HELP!! [message #362899 is a reply to message #362896] Thu, 04 December 2008 07:45 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Welcome to the forum. Please spend some time in reading the forum guidelines on how to format your post.

Quote:

SQL> CREATE TABLE DISCOUNT (
2 DISCOUNTID VARCHAR2(20) NOT NULL,
3 DISCOUNTTYPE VARCHAR2(20) NOT NULL,
4 AMOUNTOFDISCOUNT INTEGER NOT NULL,
5 CONSTRAINT PK_DISCOUNT PRIMARY KEY (DISCOUNTID),
6 FOREIGN KEY (USERID) REFERENCES CUSTOMER(USERID) );
FOREIGN KEY (USERID) REFERENCES CUSTOMER(USERID) )
*
ERROR at line 6:
ORA-00904: "USERID": invalid identifier


Regarding your problem oracle is complaining it cannot find the column because you have not created the column userid in the discount table.

Regards

Raj
Re: SQL-PLUS FOREIGN KEY HELP!! [message #362902 is a reply to message #362899] Thu, 04 December 2008 07:50 Go to previous messageGo to next message
roberthurley14
Messages: 13
Registered: December 2008
Junior Member
but its a foreign key im not trying to create it in that table im trying to get it to relate to a key in a another table
Re: SQL-PLUS FOREIGN KEY HELP!! [message #362903 is a reply to message #362902] Thu, 04 December 2008 07:54 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
To have a foreign ey relationship between tables DISCOUNT and CUSTOMER, you need to specify a column in the table DISCOUNT that will map to a column in the table CUSTOMER

Your syntax:
FOREIGN KEY (USERID) REFERENCES CUSTOMER(USERID) 

tells oracle that you want to relate DISCOUNT.USERID to CUSTOMER.USERID
This requires that the column USERID exists in DISCOUNT, and that the column USERID exists and has a primary key or unique constraint on it in CUSTOMER.

I'm guessing that you come from a different DB background where this syntax would have added the USERID column to DISCOUNT implicitly.
Re: SQL-PLUS FOREIGN KEY HELP!! [message #362904 is a reply to message #362896] Thu, 04 December 2008 07:58 Go to previous messageGo to next message
roberthurley14
Messages: 13
Registered: December 2008
Junior Member
oook could you show me how i can re-code it so it'll work??

im using oracle plus 9.2.0.1.0

[Updated on: Thu, 04 December 2008 08:00]

Report message to a moderator

Re: SQL-PLUS FOREIGN KEY HELP!! [message #362905 is a reply to message #362904] Thu, 04 December 2008 08:01 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@roberthurley14,

Simply add a column called USERID column in the DISCOUNT table in your CREATE TABLE Statement(if you haven't created the table DISCOUNT already) if you want to relate DISCOUNT.USERID to CUSTOMER.USERID.

Regards,
Jo

[Updated on: Thu, 04 December 2008 08:02]

Report message to a moderator

Re: SQL-PLUS FOREIGN KEY HELP!! [message #362906 is a reply to message #362904] Thu, 04 December 2008 08:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
CREATE TABLE DISCOUNT (
USERID VARCHAR2(80),
DISCOUNTID VARCHAR2(20) NOT NULL,
DISCOUNTTYPE VARCHAR2(20) NOT NULL,
AMOUNTOFDISCOUNT INTEGER NOT NULL,
CONSTRAINT PK_DISCOUNT PRIMARY KEY (DISCOUNTID),
FOREIGN KEY (USERID) REFERENCES CUSTOMER(USERID) );


You can make USERID not null if you wish.

how are you planning to populate USERID in CUSTOMER - is it ever going to change?
Re: SQL-PLUS FOREIGN KEY HELP!! [message #362939 is a reply to message #362896] Thu, 04 December 2008 11:18 Go to previous messageGo to next message
roberthurley14
Messages: 13
Registered: December 2008
Junior Member
yeh i have inserts for all of my columns, they all work grand and dandy its just the FK thats causing problems! can some one recode a bit of it so i can get a jist of what i should do?????
Re: SQL-PLUS FOREIGN KEY HELP!! [message #362941 is a reply to message #362896] Thu, 04 December 2008 11:29 Go to previous messageGo to next message
roberthurley14
Messages: 13
Registered: December 2008
Junior Member
i need to make the USERID in customer related to the data in DISCOUNTID in discount! some one please recode it for me im completely stumped!
Re: SQL-PLUS FOREIGN KEY HELP!! [message #362942 is a reply to message #362906] Thu, 04 December 2008 11:31 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@roberthurley14,

So this code didn't work?
JRowbottom wrote on Thu, 04 December 2008 08:04
CREATE TABLE DISCOUNT (
USERID VARCHAR2(80),
DISCOUNTID VARCHAR2(20) NOT NULL,
DISCOUNTTYPE VARCHAR2(20) NOT NULL,
AMOUNTOFDISCOUNT INTEGER NOT NULL,
CONSTRAINT PK_DISCOUNT PRIMARY KEY (DISCOUNTID),
FOREIGN KEY (USERID) REFERENCES CUSTOMER(USERID) );


You can make USERID not null if you wish.



And what about the question JRowbottom asked?
Quote:

how are you planning to populate USERID in CUSTOMER - is it ever going to change?



What problems are you exactly facing now?

Regards,
Jo
Re: SQL-PLUS FOREIGN KEY HELP!! [message #362945 is a reply to message #362896] Thu, 04 December 2008 11:36 Go to previous messageGo to next message
roberthurley14
Messages: 13
Registered: December 2008
Junior Member
would it make sense for me to just rename all column IDs (eg RECEIPTID,DISCOUNTID) to USERID to relate the USERID column from each to one another


.i.e.

CREATE TABLE DISCOUNT (
USERID VARCHAR2(20) NOT NULL,
DISCOUNTTYPE VARCHAR2(20) NOT NULL,
AMOUNTOFDISCOUNT INTEGER NOT NULL,
CONSTRAINT PK_DISCOUNT PRIMARY KEY (USERID),
FOREIGN KEY (USERID) REFERENCES CUSTOMER(USERID) );

[Updated on: Thu, 04 December 2008 11:36]

Report message to a moderator

Re: SQL-PLUS FOREIGN KEY HELP!! [message #362947 is a reply to message #362942] Thu, 04 December 2008 11:39 Go to previous messageGo to next message
roberthurley14
Messages: 13
Registered: December 2008
Junior Member
joicejohn wrote on Thu, 04 December 2008 11:31
@roberthurley14,

So this code didn't work?
JRowbottom wrote on Thu, 04 December 2008 08:04
CREATE TABLE DISCOUNT (
USERID VARCHAR2(80),
DISCOUNTID VARCHAR2(20) NOT NULL,
DISCOUNTTYPE VARCHAR2(20) NOT NULL,
AMOUNTOFDISCOUNT INTEGER NOT NULL,
CONSTRAINT PK_DISCOUNT PRIMARY KEY (DISCOUNTID),
FOREIGN KEY (USERID) REFERENCES CUSTOMER(USERID) );


You can make USERID not null if you wish.



And what about the question JRowbottom asked?
Quote:

how are you planning to populate USERID in CUSTOMER - is it ever going to change?



What problems are you exactly facing now?

Regards,
Jo


The problem is that its a new field and im inserting the values (eg INSERT INTO CUSTOMER )
VALUES (
'gfdr',
'Jay',
'Johnstone',
'12 Dream Road',
'Blessington',
'Blessington',
'Charlevielle',
'31',
'IRL',
'084-744-77-77'
);
adding a new field will throw my inserts of and they will be empty
Re: SQL-PLUS FOREIGN KEY HELP!! [message #362948 is a reply to message #362941] Thu, 04 December 2008 11:42 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@roberthurley14,

roberthurley14 wrote on Thu, 04 December 2008 11:29
i need to make the USERID in customer related to the data in DISCOUNTID in discount! some one please recode it for me im completely stumped!


My Apologies. I didn't see this post. Do you want to link CUSTOMER.USERID with DISCOUNT.DISCOUNTID? Frankly, that is a weird relaation. Please understand that both these keys are Primary Keys in their respective tables. So each othem uniquely identify the corresponding tables records.

Can you confirm you want to link CUSTOMER.USERID with DISCOUNT.DISCOUNTID? Think over it based on the data that is going to be populated in those two tables. Will this relation be valid?

I do hope I didn't misunderstand your requirement.

You can read about Foreign Keys here.

Regards,
Jo
Re: SQL-PLUS FOREIGN KEY HELP!! [message #362951 is a reply to message #362947] Thu, 04 December 2008 11:48 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@roberthurley14,,
roberthurley14 wrote on Thu, 04 December 2008 11:39
joicejohn wrote on Thu, 04 December 2008 11:31
@roberthurley14,
adding a new field will throw my inserts of and they will be empty



Let me be on the same page here as you. Why are you adding any new column in CUSTOMER table. I thought you wanted the foreign key to be present in DISCOUNT table? So we suggested you to add the required column in the DISCOUNT table.

And I didn't understand the above quote clearly. Can you explain?

Regards,
Jo

[Updated on: Thu, 04 December 2008 11:48]

Report message to a moderator

Re: SQL-PLUS FOREIGN KEY HELP!! [message #362956 is a reply to message #362896] Thu, 04 December 2008 12:08 Go to previous messageGo to next message
roberthurley14
Messages: 13
Registered: December 2008
Junior Member
yes thats basically what im going to do but change DISCOUNTID's name to USERID in the discount table as to relate them, should that work?
Re: SQL-PLUS FOREIGN KEY HELP!! [message #362961 is a reply to message #362956] Thu, 04 December 2008 12:35 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@roberthurley14,

NO!!! A very bad idea. This means wherever you are facing this error with the foreign key you are going to rename the primary key column of that table with the foreign key column. You will end up cursing yourself if you do it. It will disrupt the relation between the tables.

Bast way is to add the column to the table in which you want to add up a foreign key as suggested earlier by others.

I think , you need to understand the need of PRIMARY KEY and FOREIGN KEY in a relational database. Brush up the RDBMS Concepts.

Regards,
Jo

[Updated on: Thu, 04 December 2008 12:37]

Report message to a moderator

Re: SQL-PLUS FOREIGN KEY HELP!! [message #362963 is a reply to message #362896] Thu, 04 December 2008 12:41 Go to previous messageGo to next message
roberthurley14
Messages: 13
Registered: December 2008
Junior Member
Ok i THINK i finally fixed it, ps u were right that last idea was VERY VERY bad lol! ok so here it is, tell me if this works/makes sense it worked in oracle with no errors but i dont know if its going to relate the tables:::::

CREATE TABLE CUSTOMER (
USERID VARCHAR2(80) NOT NULL,
FIRSTNAME VARCHAR2(80) NOT NULL,
LASTNAME VARCHAR2(80) NOT NULL,
ADDR1 VARCHAR2(80) NOT NULL,
ADDR2 VARCHAR2(40),
CITY VARCHAR2(80) NOT NULL,
STREET VARCHAR2(80) NOT NULL,
POSTCODE VARCHAR2(20) NOT NULL,
COUNTRY VARCHAR2(20) NOT NULL,
PHONE VARCHAR2(80) NOT NULL,
CONSTRAINT PK_CUSTOMER PRIMARY KEY (USERID)
);

CREATE TABLE DISCOUNT (
DISCOUNTID VARCHAR2(20) NOT NULL,
DISCOUNTTYPE VARCHAR2(20) NOT NULL,
AMOUNTOFDISCOUNT INTEGER NOT NULL,
CONSTRAINT PK_DISCOUNT PRIMARY KEY (DISCOUNTID),
FOREIGN KEY (DISCOUNTID) REFERENCES CUSTOMER(USERID) );


the underlined bit is what i changed, originally i had USERID in that bit now i changed it to DISCOUNTID, is that relation DISCOUNTID to USERID or am i wrong again??

also thanks for the help...

now im getting this error on the 3 other tables i have, discount and customer work but when i try to input values to my other columns i get this :

ORA-02291: integrity constraint (S.SYS_C004155) violated - parent key not found

[Updated on: Thu, 04 December 2008 12:47]

Report message to a moderator

Re: SQL-PLUS FOREIGN KEY HELP!! [message #362969 is a reply to message #362896] Thu, 04 December 2008 13:09 Go to previous messageGo to next message
roberthurley14
Messages: 13
Registered: December 2008
Junior Member
if i were to do it the way i was told:

CREATE TABLE DISCOUNT (
DISCOUNTID VARCHAR2(20) NOT NULL,
USERID VARCHAR2(80) NOT NULL,
DISCOUNTTYPE VARCHAR2(20) NOT NULL,
AMOUNTOFDISCOUNT INTEGER NOT NULL,
CONSTRAINT PK_DISCOUNT PRIMARY KEY (DISCOUNTID),
FOREIGN KEY (USERID) REFERENCES CUSTOMER(USERID) );
and then when i try to insert into discount i get::
INSERT INTO DISCOUNT
VALUES (
'10',
'Ten Percent',
'10'
);
ORA-00947: not enough values

as there are now 4 fields, not three so i dont think i can do it that way, or should i just include the value from USERID in my insert?
Re: SQL-PLUS FOREIGN KEY HELP!! [message #362975 is a reply to message #362963] Thu, 04 December 2008 13:21 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
Quote:

ps u were right that last idea was VERY VERY bad


And still you implemented it. I think my explanation is not good enough to make you understand.

Ok one more try....

1. Primary Key: A set of one or more columns together which uniquely identifies the records in your table.
So for your CUSTOMER table the primary key is USERID.
And for your DISCOUNT table the primary key is DISCOUNTID.
Please note that chances of these two columns of containing the same data is very rare. (I have never encountered it before).

2. Foreign Key: A set of one or more columns in any table (not necessarily a candidate key, let alone the primary key, of that table) which may hold the value(s) found in the primary key column(s) of some other table.

So in your case you need to have a column which in DISCOUNT table that can hold the values stored in the USERID Column in the CUSTOMER Table.

Now, You made the PRIMARY KEY column of DISCOUNT table (DISCOUNTID) as a FOREIGN KEY referncing the USERID column in CUSTOMER table. That means you will be able to enter the values for DISCOUNTID which are there in the USERID Column in CUSTOMER Table only. Is this what you want?

Hope this helps,
Regards,
Jo

[Updated on: Thu, 04 December 2008 13:23]

Report message to a moderator

Re: SQL-PLUS FOREIGN KEY HELP!! [message #362978 is a reply to message #362896] Thu, 04 December 2008 13:28 Go to previous messageGo to next message
roberthurley14
Messages: 13
Registered: December 2008
Junior Member
the code that i wrote most recently, does that work? one it works i dont mind! if not can you show me how you, personally would write it without impacting on my insert code?
attached is my full code so you can understand what im doing...
  • Attachment: sqlcode.txt
    (Size: 6.20KB, Downloaded 214 times)
Re: SQL-PLUS FOREIGN KEY HELP!! [message #362984 is a reply to message #362969] Thu, 04 December 2008 13:36 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
roberthurley14 wrote on Thu, 04 December 2008 13:09
if i were to do it the way i was told:
INSERT INTO DISCOUNT
VALUES (
'10',
'Ten Percent',
'10'
);
ORA-00947: not enough values

as there are now 4 fields, not three so i dont think i can do it that way, or should i just include the value from USERID in my insert?


First Analyze your data, then create tables and relationship between the tables based on that.

My guess is that each discount is specific to a particular customer. If this is so you should include the USERID data if you have in your inserts.

INSERT INTO DISCOUNT
(DISCOUNTID, USERID, DISCOUNTTYPE, AMOUNTOFDISCOUNT)
 VALUES
('10', 'USERID VALUE', 'TEN Percent', '10');
-- Replace the USERID VALUE with the USERID Value you have


Hope this helps,
Regards,
Jo
Re: SQL-PLUS FOREIGN KEY HELP!! [message #362987 is a reply to message #362896] Thu, 04 December 2008 13:41 Go to previous messageGo to next message
roberthurley14
Messages: 13
Registered: December 2008
Junior Member
ill try it! cheers for the help!
Re: SQL-PLUS FOREIGN KEY HELP!! [message #362989 is a reply to message #362987] Thu, 04 December 2008 13:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time, Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: SQL-PLUS FOREIGN KEY HELP!! [message #362996 is a reply to message #362896] Thu, 04 December 2008 14:42 Go to previous messageGo to next message
roberthurley14
Messages: 13
Registered: December 2008
Junior Member
ok fixxed! Very Happy

CREATE TABLE CUSTOMER (
USERID NUMBER NOT NULL,
FIRSTNAME VARCHAR2(80) NOT NULL,
LASTNAME VARCHAR2(80) NOT NULL,
ADDR1 VARCHAR2(80) NOT NULL,
ADDR2 VARCHAR2(40),
CITY VARCHAR2(80) NOT NULL,
STREET VARCHAR2(80) NOT NULL,
POSTCODE VARCHAR2(20) NOT NULL,
COUNTRY VARCHAR2(20) NOT NULL,
PHONE VARCHAR2(80) NOT NULL,
PRIMARY KEY (USERID),
FOREIGN KEY(Discount_ID) REFERENCES Discount(Discount_ID));


ALTER TABLE CUSTOMER
ADD EMAIL VARCHAR(80);


CREATE TABLE TOY (
TOYID NUMBER NOT NULL,
 TOYNAME VARCHAR2(80) NOT NULL,
 DESCRIPTION VARCHAR2(255) NOT NULL,
CATEGORY VARCHAR(80) NOT NULL,
AGERANGE INTEGER,
PRIMARY KEY (TOYID),
CHECK (CATEGORY IN(‘Baby’, ‘Pre-School’, ‘Boys Toys’, ‘Girls Toys’, ‘Lego’, ‘Games’, ‘Books’));













CREATE TABLE STAFF (
STAFFID NUMBER NOT NULL,
STAFFNAME VARCHAR2(80) NOT NULL,
STAFFADDR1 VARCHAR2(255) NOT NULL,
STAFFADDR2 VARCHAR2(255) NOT NULL,
STAFFADDR3 VARCHAR2(255) NOT NULL,
SALARY INTEGER,
STAFFSECTION VARCHAR2(30) NOT NULL,
PRIMARY KEY (STAFFID),
FOREIGN KEY (RECEIPTID) REFERENCES RECEIPT(RECEIPTID)
CHECK (STAFFID Between 1 and 999));


CREATE TABLE RECEIPT (
RECEIPTID NUMBER NOT NULL,
DATEOFISSUE date NOT NULL,
PURCHASES VARCHAR2(200) NOT NULL,
TOTALCOST NUMBER NOT NULL,
PRIMARY KEY (RECEIPTID));



CREATE TABLE DISCOUNT (
DISCOUNTID NUMBER NOT NULL,
DISCOUNTTYPE VARCHAR2(20) NOT NULL,
AMOUNTOFDISCOUNT INTEGER NOT NULL,
CONSTRAINT PK_DISCOUNT PRIMARY KEY (DISCOUNTID) );

CREATE TABLE CUSTOMER_TOY (
CUSTOMERID NUMBER NOT NULL,
TOYID NUMBER NOT NULL,
PRIMARY KEY(CUSTOMERID, TOYID));

CREATE TABLE STAFF_TOY
(STAFFID NUMBER NOT NULL,
TOYID NUMBER NOT NULL,
PRIMARY KEY(STAFFID, TOYID));
Re: SQL-PLUS FOREIGN KEY HELP!! [message #363005 is a reply to message #362969] Thu, 04 December 2008 15:14 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
roberthurley14 wrote on Thu, 04 December 2008 14:09

INSERT INTO DISCOUNT
VALUES (
'10',
'Ten Percent',
'10'
);
ORA-00947: not enough values



Another bad design. Now if you add a column to the table, this code will fail miserably. Always explicitly name columns in an INSERT statement.

You want another bad design tip?
How many times are you going to change the datatypes in the DISCOUNT table? Your data does not match your datatypes. Implicit conversion will bite you in the ass one day. Not "if," but "when."
Previous Topic: ORA-00922: missing or invalid option (merged 3)
Next Topic: date format to dd-mm-yyyy
Goto Forum:
  


Current Time: Mon Dec 05 21:33:27 CST 2016

Total time taken to generate the page: 0.09503 seconds