Cascade error

[Winterwolf73]

Ok all, I have no idea what I did. But I got the relationships set up. However, now when I add the customer number, I get the following error.

Cannot perform cascading operation. It would result in a duplicate key in table.

I have attached a copy of the DB for reference.

 

[plog]

Your 1-many relationships are incorrect and you are not using your primary keys correctly.

When you make a 1-many relationship between tables (e.g. tblCustInfo / tblProjects) the primary key value of the 1 table (tblCustInfo) goes into the many table (tblProjects). So, Project_ID needs to come out of tblCustInfo and a field to hold the ID value of tblCustInfo should be made in tblProjects.

Here's a good link explaining primary/foreign keys:

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

Additionaly, you shouldn't have a 1-1 relationship, instead you simply put all the data into 1 table. You have a 1-1 between tblCustInfo and tblInvoices--if its true a customer can have just 1 invoice, then put all those fields in 1 table. But it doesn't seem correct--can't a customer have multiple invoices?

Lastly, no spaces or special characters in names, it just makes coding/querying that more difficult. Only use alphanumeric characters and underscores ([Type Of Payment] - TypeOfPayment)

 

[Winterwolf73]

So it would be better if I went from tblCustInfo to tblInvoices(1 - Many) as each customer could have multiple invoices. I could eliminate tblProjects as this would be covered by the invoices table.

 

[plog]

Possibly. I don't fully understand how your organization operates. Answer these---

1. Will there be only 1 invoice per project?
2. Could one invoice contain data from multiple projects?

At a quick glance those can seem like the exact same question but the are different.

 

[Winterwolf73]

To answer your questions,
1. There will only be one invoice per project.
2. it all depend. But, more than likely not.

 

[plog]

If an invoice can contain data from multiple projects you will need an invoice table