That is what I would have thought as well. It is exactly why I'm putting in the constraints now, because bad data like that had gotten into the database and I was hunting it down and removing it, as well as putting in constraints to keep it from happening again. In other joins like this I created in the table, this was the problem, and once I had removed incorrect records, I was able to create the joins.
But when I ran a query to look for those kinds of records I couldn't find any. Using my simple example again. A simple example Color Category Table ID Name 1 Red 2 Blue 3 Green Product Detail Table ID Name Color 1 Bike 1 2 Bike 2 3 Trike 1 4 Trike 2 5 Unicyc 1 I would run the query SELECT * FROM Color LEFT OUTER JOIN Product ON Product.ID = Color.ID WHERE Color.ID IS NULL This query would return no records, but the opposite. SELECT * FROM Product LEFT OUTER JOIN Color ON Color.ID = Product.ID WHERE Product.ID IS NULL Would return records, in this example: 3 Green That is what confused me, I would think that the second example wouldn't matter in creating this relationship/constraint. -------------- Ian Skinner Web Programmer BloodSource Sacramento, CA -----Original Message----- From: Bryan Stevenson [mailto:[EMAIL PROTECTED] Sent: Thursday, June 05, 2003 7:54 AM To: SQL Subject: Re: Relational Design Question. Yep...I'm with Jeff....You most likely have an FK ID that isn't in the parent table (i.e. in your simple example....a color ID that doesn't exist in the Color table) Bryan Stevenson B.Comm. VP & Director of E-Commerce Development Electric Edge Systems Group Inc. t. 250.920.8830 e. [EMAIL PROTECTED] --------------------------------------------------------- Macromedia Associate Partner www.macromedia.com --------------------------------------------------------- Vancouver Island ColdFusion Users Group Founder & Director www.cfug-vancouverisland.com ----- Original Message ----- From: "Jeffry Houser" <[EMAIL PROTECTED]> To: "SQL" <[EMAIL PROTECTED]> Sent: Thursday, June 05, 2003 4:55 AM Subject: RE: Relational Design Question. > Is there data already in these tables? > It makes me think that the product table has a ColorID that does not > exist in the Color table. > > Or from your error message, it seems more specifically that the Are table > has an ID that isn't in the Location_Area table doesn't. (or vice versa if > I'm mixing up the relation ) > > At 10:27 PM 6/4/2003 -0700, you wrote: > >This is the error message I receive. > > > >/* > > Wednesday, June 04, 2003 10:10:56 PM > > Database: SierraOutdoorReceation > > Application: MS SQLEM - Data Tools > >*/ > > > >'Area (SierraOutdoorReceation)' table saved successfully > >'Location_Area_Join' table > >- Unable to create relationship 'FK_Location_Area_Join_Area'. > >ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE > >statement conflicted with COLUMN FOREIGN KEY constraint > >'FK_Location_Area_Join_Area'. The conflict occurred in database > >'SierraOutdoorReceation', table 'Area', column 'ID'. > > > >I tried removing the PK from the Area (category) table. That seemed > >improper to me, but I tried it. As I expected I couldn't define any > >relationship with a PK. So I put it back. I got the relationship in, I > >just unchecked the "verify existing data" option when I defined the > >relationship. That seems odd to me, why couldn't it verify the data if > >there are records in the category table that are not currently used in the > >detail table. > > > >Thanks for you help. > > > >Ian Skinner > >Web Programmer > >Sierra Outdoor Recreation > >www.SierraOutdoorReceation.com <http://www.SierraOutdoorReceation.com> > > > > > > _____ > > > >I've stopped 11,192 spam messages. You can too! > >Get your free, safe spam protection at http://www.cloudmark.com/spamnetsig/} > > > >-----Original Message----- > >From: Ian Skinner [mailto:[EMAIL PROTECTED] > >Sent: Wednesday, June 04, 2003 2:35 PM > >To: SQL > >Subject: Relational Design Question. > > > > > >I've got a DBA Relational Design question. I've got two tables, one a > >category table, and the other a detail table where one field is a Foreign > >Key to the category table. In my DBMS (SQL Server 2000), I am trying to set > >up this relationship/constraint. It is not allowing me to do this. It > >gives me an error, because there are category records in the category table > >that have not yet been used in the detail table. Is this correct behavior, > >or am I doing something wrong? When creating a one to many relationship > >between tables, must you have matching keys for all records on both sides? > > > >A simple example > > > >Color Category Table > >ID Name > >1 Red > >2 Blue > >3 Green > > > >Product Detail Table > >ID Name Color > >1 Bike 1 > >2 Bike 2 > >3 Trike 1 > >4 Trike 2 > >5 Unicyc 1 > > > >I am unable to create a relationship in this case, because there are no > >records in the Detail table that yet use the color Green. This can't be > >correct? If it is not correct, what may I be doing to cause the error. > > > > > >-------------- > >Ian Skinner > >Web Programmer > >BloodSource > >Sacramento, CA > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=6 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=6 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm
