Upsize trouble

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike Long - 24 Sep 2004 05:19 GMT

I am upsizing an Access db to SQL. I have these tables: Products, Brands,
Colors, a Junction table between Products and Colors, and a Junction table
between Brands and Colors. They link in a circle. The idea is to be able to
pick a brand from a drop list and have only the appropriate colors for that
brand display when entering products.Everything seems to work in the linked
mdb file, but I got these messages during the upsize.

[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement
conflicted with COLUMN FOREIGN KEY constraint 'tblJctBrandsColors_FK01'. The
conflict occurred in database 'DoughboysBE', table 'tblColors', column
'strColorID'.
[Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement
conflicted with COLUMN FOREIGN KEY constraint 'tblJctProductsColors_FK00'.
The conflict occurred in database 'DoughboysBE', table 'tblColors', column
'strColorID'

Can anyone give me a clue as to what these messages are trying to tell me? I
know FK is foreign key, but what is 00 and 01? The data types are the same
and the field sizes are identical on all sides.

I tried making an adp, but nothing worked, and my client is the only person
doing input, so there is no need for multiple connections. These products
will eventually be on a web based ordering system.

Reply to this Message

MGFoster - 24 Sep 2004 20:28 GMT

The errors seem to indicate that there are 2 FKs on
tblColors.strColorID. I'd remove both FKs in Access, update the db,
then create the correct FK in the SQL db.

Signature

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> I am upsizing an Access db to SQL. I have these tables: Products, Brands,
> Colors, a Junction table between Products and Colors, and a Junction table
[quoted text clipped - 19 lines]
> doing input, so there is no need for multiple connections. These products
> will eventually be on a web based ordering system.

Reply to this Message

Mike Long - 24 Sep 2004 21:54 GMT

Yes there certainly are 2 foreign keys. Is that a no-no in SQL server? How
else do you relate one parent table to 2 child tables?

Thank you

> The errors seem to indicate that there are 2 FKs on
> tblColors.strColorID. I'd remove both FKs in Access, update the db,
[quoted text clipped - 13 lines]
> > [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement
> > conflicted with COLUMN FOREIGN KEY constraint

'tblJctProductsColors_FK00'.

> > The conflict occurred in database 'DoughboysBE', table 'tblColors', column
> > 'strColorID'
[quoted text clipped - 6 lines]
> > doing input, so there is no need for multiple connections. These products
> > will eventually be on a web based ordering system.

Reply to this Message

Sylvain Lafontaine - 24 Sep 2004 23:03 GMT

The problem is on the upsizing wizard side, not on the SQL-Server.

S. L.

> Yes there certainly are 2 foreign keys. Is that a no-no in SQL server? How
> else do you relate one parent table to 2 child tables?
[quoted text clipped - 42 lines]
> products
>> > will eventually be on a web based ordering system.

Reply to this Message