?
Solved

help with relationship constraints

Posted on 2014-10-09
1
Medium Priority
?
120 Views
Last Modified: 2014-10-14
I'm hoping I can get some help with setting up my relationship constraints.  I am setting up my database which has an account table, bank table, contact table, address table and address type table (mailing, shipping, etc.) (see attached image).

I have a one-to-many relationship setup from bank to address type, account to address type and contact to address type and address to address type.  If you look at the foreign key relationship for the FK_AddressType_Account, the foreign key table is address type (ownerId column) and the primary/unique key table is account (Id column).  The owerId column in the address type can be a bank record, account record or contact record.  The problem is that an address has to be entered when adding a bank, account, or contact.  That should be able to be added on an insert or update.  So, what am I doing incorrectly that wouldn't allow data to be inserted?

10-9-2014-4-46-06-PM.jpg
Any help is appreciated.
Thank you.
0
Comment
Question by:-Dman100-
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 1

Accepted Solution

by:
nebb-tgr earned 2000 total points
ID: 40372409
Hello.

The whole point of foreign key constraint is to:
- Verify that the number you put into this column actually exists in the foreign table.
- Not allow you to destroy data by removing data from other tables that are linked.

This means that you need the underlying data before you add a value into the foreign key column.

You can however allow nulls in that column.

AddressTypeId INT NULL FOREIGN KEY REFERENCES AddressType (Id)

Open in new window

0

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question