Enforce table relation

Posted on 2007-11-23
Last Modified: 2012-05-05
I have an "address" table in a DB that is related to several other tabels. at present these relations are not enforced. I would like to enforce the relationships but I am getting an error because there is no record with ID of "0" in the parent table. How might I resolv this issue. Do I need to create tabels and seporate the records?

CREATE TABLE [dbo].[Address](
      [addressId] [bigint] IDENTITY(1,1) NOT NULL,
      [address1] [varchar](50) NULL,
      [merchantId] [varchar](20) NULL,
      [personId] [bigint] NULL,
      [clubId] [bigint] NULL,
      [labelId] [bigint] NULL,
      [studioId] [bigint] NULL,
      [userId] [nvarchar](50) NULL,
      [orderId] [bigint] NULL)
Question by:ruffone
LVL 12

Accepted Solution

needo_jee earned 200 total points
ID: 20342044
you are trying to enfore parent child relation between two tables where child tables seems having an orphan record and that might be with id =0 but there is no record in parent with that id.

would be better to generate new script for complete database
create a new database
apply relationships
and move data from origional database to new one.
otherwise you need to make data valid for relationships you want to enforce
LVL 15

Assisted Solution

dosth earned 200 total points
ID: 20342873
Yes, JEE is correct

Check all records in child tables and see there is a record in parent table, so there is a record in child table with a parent id and there is not records in parent table, you cant do this

Author Comment

ID: 20343528
I know what the problem is. I am trying to figure out the best solution. This is what the records look like. Below I am showing records in the "Address" table for the "Merchant", "people" and "Clubs" tables and this is typical. Is the solution as simple as just adding a "0" record to the parent table or do I have to make a "Merchant_Address", "People_Address", "Club_Address" table and separate the records for a solid long-term solution.

[addressId] [merchantId] [personId] [clubId]
1      0                 0                     1
2      1                 0                     0
3      0                 1               0      
4      0                 0               0

LVL 23

Expert Comment

ID: 20346195
<<do I have to make a "Merchant_Address", "People_Address", "Club_Address" table and separate the records for a solid long-term solution.>>
Normalizing your schema is the only long term solution...each of your columns looks like itt should be a table...
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!


Author Comment

ID: 20353267
isn't that a lot of tabels having the same address information?
LVL 23

Expert Comment

ID: 20353300
<<isn't that a lot of tabels having the same address information?>>
What is a *lot of tables* ? If  2 , 3 extra tables created *once* can prevent all the problems of one denormalized set bag I say it's not much...

Hope this helps...

Author Comment

ID: 20353671
I am not complaining about the amount of tables as I am the replication of data. I would instead of having one table with addresses I would have 6 tables with addresses. Is that what you are sugesting?
LVL 23

Assisted Solution

Racim BOUDJAKDJI earned 100 total points
ID: 20354082
<Is that what you are sugesting?>>
What I am suggesting is that the drawbacks you will draw from having a denormalized schema (with less tables) *far* outweigths any advantage you may draw from that choice (such as updating your replication subs --> You do it *once* by clicking on some extra tables that is not what I call unbearable)...

It is very difficult to give online advice about design not knowing the full spectrum of business requirements.  I don't know for instance if a person can have 1 or many adresses or if it may have no adresses at all ?  Assuming that is the case then you would look at something like....

--In the case it may have 1 or many addresses but not 0 the structure may look like
Adress: Address
Person: FirstName, Address--(I suppose here that a Person always has least ONE address)
Merchant: MerchantName, Address --(I suppose here that a Merchant always has least ONE address)
Club: ClubName, Address--(I suppose here that a Club always has least ONE address)

--In the case a person may or may not have an address then you have to decompose the Person table into Person_WithAdresses Table and enforce the relationship with Person...Which would come back to something like...

Adress: Address
Person: FirstName, LastName --(I suppose here that a Person may have 0 address)
Person_WithAdresses: FirstName, LastName, Address(FK to Adress)
Merchant: MerchantName, Address--(I suppose here that a Merchant always has least ONE address)
Club: ClubName, Address--(I suppose here that a Club always has least ONE address)

One direct advantage of that approach is that you claim back all the space of the *0* you had in your previous table...

That can give you an idea on what to do to normalize your schema...Not convinced, suppose you want to count how many merchants and people have the same address...Such request on the other hand is very simple to do using the schema I am suggesting...

select count(*) from Person_WithAdresses inner join Merchant on Person_WithAdresses.Address = Merchant.Address...

As you can see, it is fairly easy...Now, try to get the same info using your old structure and you will comprehend what a long term solution is...

Hope this helps...

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

706 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

19 Experts available now in Live!

Get 1:1 Help Now