Solved

help with relationship constraints

Posted on 2014-10-09
1
102 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-
1 Comment
 
LVL 1

Accepted Solution

by:
nebb-tgr earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now