Constraint Help Please

[Trogan]

Hi,

I have some tables (below), but I'm having trouble entering multiple lines of data.

Here are the tables I have created.

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...

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?

Any solution or advice would be greatly appreciated. Thank you!

I am also fairly new to database systems.

[Sulley's Boo]

<_<

Remove the PK (invoice_no, ISBN) from the Delivery table so that you can repeat the invoice_no as much as you want.

Think of a new PK for the detail (delivery) table. How about Serial no?

[Trogan]

Hi Sulley, thank you for the reply.

I will remove the PK (invoice_no, ISBN) from the Delivery table and put something in it's place and see how it goes.

Thanks again! :)

[Trogan]

It didn't work.

I should have mentioned, I need to enter multiple data into the invoice table using the same invoice_no.

Any other suggestions please?

[jwenting]

So you've made a serious error in your datamodel.
Apparently a record in the invoice table does not represent an invoice but a line item.
Redesign your database to match that, with a new invoice table containing only items that are not repeated (like the invoice number), and a lineitem table containing a foreign key into to the invoice table to the invoice number.

[Sulley's Boo]

You can overcome that problem by having a composite key (2 PKs) for the invoice table .. where you'll be able to repeat the invoice number BUT not the other Primary key .. :?:

Can you show us your ERD?

[jwenting]

yes, but that would leave you with a substandard database model.
You'd have to duplicate data in each record, a bad idea.

[Sulley's Boo]

<_<

do what jwenting said .. 1 master table (invoice) and a transaction table linked to it (one to many relationship) ..

[Trogan]

Forgot to reply, but I managed to get things working by making some minor changes to the tables.

[jinternalframe]

Hey good that you made your stuff work. But keep in mind this thing - The databases are only the repositoires of data. It is you who will have to decide what data goes in where and make sure that you use the capabilities of the database to reflect the exact needs of your data model (your data). Making ad-hoc changes might work bu they may not be fruitful in the long run. Also, analyse the model before you even try to create any tables in the database. That will definitely help in the long run.