April 8th, 2008, 01:34 PM
|
|
Registered User
|
|
Join Date: Apr 2008
Posts: 9
Time spent in forums: 1 h 53 m 38 sec
Reputation Power: 0
|
|
|
Constraints Help Please
Hi,
I have some tables (below), but I'm having trouble entering multiple lines of data.
Here are the tables I have created.
Quote: CUSTOMER table
SQL> create table customer
2 (customer_no char(6) not null,
3 name varchar2(30) not null,
4 address varchar2(50) not null,
5 credit_limit number(6,2),
6 constraint customer_customer_no_pk primary key (customer_no));
Table created.
SALESMAN table
SQL> create table salesman
2 (salesman_id char(8),
3 name varchar2(20) not null,
4 address varchar2(50) not null,
5 emaill_address varchar2(30),
6 constraint salesman_salesman_id_pk primary key (salesman_id));
Table created.
ITEM table
SQL> create table item
2 (ISBN char(13) not null,
3 title varchar2(30) not null,
4 price number(4,2) not null,
5 constraint item_ISBN_pk primary key (ISBN));
Table created.
INVOICE table
SQL> create table invoice
2 (invoice_no char(1) not null,
3 invoice_date date not null,
4 salesman_id char(8),
5 customer_no char(6) not null,
6 ISBN char(13) not null,
7 Nos varchar2(2) not null,
8 credit_limit number(6,2),
9 payment_type varchar2(6) not null,
10 constraint invoice_invoice_no_pk primary key (invoice_no),
11 constraint invoice_salesman_id_fk foreign key (salesman_id)
12 references salesman(salesman_id),
13 constraint invoice_customer_no_fk foreign key (customer_no)
14 references customer(customer_no),
15 constraint invoice_ISBN_fk foreign key (ISBN)
16 references item(ISBN));
Table created.
DELIVERY table
SQL> create table delivery
2 (invoice_no char(1) not null,
3 ISBN char(13) not null,
4 constraint delivery_invoice_no_ISBN_cpk primary key (invoice_no,ISBN),
5 constraint delivery_invoice_fk foreign key (invoice_no)
6 references invoice(invoice_no),
7 constraint delivery_ISBN_fk foreign key (ISBN)
8 references item(ISBN));
Table created.
| I can enter data into the customer, salesman and item table without any problems. However, I need to enter multiple lines of data using the same invoice_no (which is 1). The data is generally the same, except for 2 things need changing but I keep getting the following error...
Quote: | ORA-00001: unique constraint (SYSTEM.INVOICE_INVOICE_NO_PK) violated |
I understand that I can only use invoice_no once since it is a unique key.
Is there a way of getting around this problem or a possible solution?
Any solution or advice would be greatly appreciated.
Thank you!
|
Dev Shed Forums Sponsor:
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
