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 This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com
