|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > SQL Relationships in ADP
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL Relationships in ADP
|
|
| John Yang 2002-11-09, 11:23 pm |
| I was moving my Access database over to SQL 2000 for my company
yesterday until I realized that there was this one little problem that
made me revert back to Access. I had the intranet running against
sql, everything in Access worked fine except for one major thing.
Very frustrating!
There are two major tables that I use. Table1 holds address
information of our clients. The table's primary key is ID. Table2 is
the foreign key table to Table1 and it also has ID as the primary key.
Table2 holds the list of purchases that our clients made.
I created a form in Access where the data enterer can enter and update
information on both tables. Since I created a relationship in Access,
I had no problems updating and creating new records within that form.
If we have a new client, we update Table1. If that client makes a
purchase we update Table2. So it doesn't necessarily mean that an
entry in table1 will require any data entering in table2. Here's
where the problem comes in when I upsize my database to SQL:
The form will let me INSERT information to table1 but will not let me
INSERT information to table2. If I try to insert information into
table2 when using the form I created, it keeps bringing up this error
message telling me that there's a write conflict. However, I'm able
to update information within table2 with existing records that were
created prior to the conversion over to sql. I never had this problem
in Access as a standalone database. What am I doing wrong?
There is some kind of problem with the relationship created between
these two tables. It won't even let me create a relationship in SQL
unless I uncheck the "check existing information on creation". I
know how to create joins in sql, so that's not the problem. It's
something with the foreign key constraints of some sort. Here's the
message that I get when I try to create the relationship without
uncheking the "check existing information on creation" check box when
I'm creating a diagram:
Unable to create relationship 'FK_table2_table1'.
ADO error: ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
constraint 'FK_table2_table1'. The conflict occurred in database
'CUSTOMERS', table ‘Table1', column 'ID'.
Please reply to this post if you have a solution. I would greatly
appreciate some help on this. Thanks.
| |
| Ricky Artigas 2002-11-10, 12:23 am |
| It seems like there is a record in table 2 whose ID is not in table 1 which
is why it won't allow you to create the foreign key relationship.
"John Yang" <jyang1977@hotmail.com> wrote in message
news:27b630a.0211092117.76e916a7@posting.google.com...
> I was moving my Access database over to SQL 2000 for my company
> yesterday until I realized that there was this one little problem that
> made me revert back to Access. I had the intranet running against
> sql, everything in Access worked fine except for one major thing.
> Very frustrating!
>
> There are two major tables that I use. Table1 holds address
> information of our clients. The table's primary key is ID. Table2 is
> the foreign key table to Table1 and it also has ID as the primary key.
> Table2 holds the list of purchases that our clients made.
>
> I created a form in Access where the data enterer can enter and update
> information on both tables. Since I created a relationship in Access,
> I had no problems updating and creating new records within that form.
> If we have a new client, we update Table1. If that client makes a
> purchase we update Table2. So it doesn't necessarily mean that an
> entry in table1 will require any data entering in table2. Here's
> where the problem comes in when I upsize my database to SQL:
>
> The form will let me INSERT information to table1 but will not let me
> INSERT information to table2. If I try to insert information into
> table2 when using the form I created, it keeps bringing up this error
> message telling me that there's a write conflict. However, I'm able
> to update information within table2 with existing records that were
> created prior to the conversion over to sql. I never had this problem
> in Access as a standalone database. What am I doing wrong?
>
> There is some kind of problem with the relationship created between
> these two tables. It won't even let me create a relationship in SQL
> unless I uncheck the "check existing information on creation". I
> know how to create joins in sql, so that's not the problem. It's
> something with the foreign key constraints of some sort. Here's the
> message that I get when I try to create the relationship without
> uncheking the "check existing information on creation" check box when
> I'm creating a diagram:
>
> Unable to create relationship 'FK_table2_table1'.
> ADO error: ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
> constraint 'FK_table2_table1'. The conflict occurred in database
> 'CUSTOMERS', table 'Table1', column 'ID'.
>
> Please reply to this post if you have a solution. I would greatly
> appreciate some help on this. Thanks.
| |
| Uri Dimant 2002-11-10, 1:23 am |
| John
Perhpas this constain already exists in database
Ricky Artigas <ricky_artigas@yahoo.com> wrote in message
news:OrLb6#HiCHA.1392@tkmsftngp12...
> It seems like there is a record in table 2 whose ID is not in table 1
which
> is why it won't allow you to create the foreign key relationship.
>
>
> "John Yang" <jyang1977@hotmail.com> wrote in message
> news:27b630a.0211092117.76e916a7@posting.google.com...
> > I was moving my Access database over to SQL 2000 for my company
> > yesterday until I realized that there was this one little problem that
> > made me revert back to Access. I had the intranet running against
> > sql, everything in Access worked fine except for one major thing.
> > Very frustrating!
> >
> > There are two major tables that I use. Table1 holds address
> > information of our clients. The table's primary key is ID. Table2 is
> > the foreign key table to Table1 and it also has ID as the primary key.
> > Table2 holds the list of purchases that our clients made.
> >
> > I created a form in Access where the data enterer can enter and update
> > information on both tables. Since I created a relationship in Access,
> > I had no problems updating and creating new records within that form.
> > If we have a new client, we update Table1. If that client makes a
> > purchase we update Table2. So it doesn't necessarily mean that an
> > entry in table1 will require any data entering in table2. Here's
> > where the problem comes in when I upsize my database to SQL:
> >
> > The form will let me INSERT information to table1 but will not let me
> > INSERT information to table2. If I try to insert information into
> > table2 when using the form I created, it keeps bringing up this error
> > message telling me that there's a write conflict. However, I'm able
> > to update information within table2 with existing records that were
> > created prior to the conversion over to sql. I never had this problem
> > in Access as a standalone database. What am I doing wrong?
> >
> > There is some kind of problem with the relationship created between
> > these two tables. It won't even let me create a relationship in SQL
> > unless I uncheck the "check existing information on creation". I
> > know how to create joins in sql, so that's not the problem. It's
> > something with the foreign key constraints of some sort. Here's the
> > message that I get when I try to create the relationship without
> > uncheking the "check existing information on creation" check box when
> > I'm creating a diagram:
> >
> > Unable to create relationship 'FK_table2_table1'.
> > ADO error: ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
> > constraint 'FK_table2_table1'. The conflict occurred in database
> > 'CUSTOMERS', table 'Table1', column 'ID'.
> >
> > Please reply to this post if you have a solution. I would greatly
> > appreciate some help on this. Thanks.
>
>
| |
| John Yang 2002-11-11, 6:23 pm |
| Hey, thanks for the response. I think you're right cause there are
records in the second table that has information that doesn't pertain
to the main table.
However, i found a temporary solution to the problem but it's still an
annoying problem that i hope you can help me with. When you click on
a table, say table1 (within access tables view), you see a plus sign
next to all the records. When you click on that plus sign, it will
show you the related record in the other table, table2. When i click
on a newly created record that's created on the main table (table1) i
have to fill in the information there for the related table, table2.
Actually, i only have to fill in one character in any of the fields,
after i click on the "+" sign and then i can delete it. This will
create an extra row for me to input new information. After that's
done, i'm able to enter in the record in my form with no problem. the
problem is, i don't want data enterers to do that everytime we have a
new customer. Is there something that i can do? Thanks for your
anticpated response.
"Ricky Artigas" <ricky_artigas@yahoo.com> wrote in message news:<OrLb6#HiCHA.1392@tkmsftngp12>...
> It seems like there is a record in table 2 whose ID is not in table 1 which
> is why it won't allow you to create the foreign key relationship.
>
>
> "John Yang" <jyang1977@hotmail.com> wrote in message
> news:27b630a.0211092117.76e916a7@posting.google.com...
> > I was moving my Access database over to SQL 2000 for my company
> > yesterday until I realized that there was this one little problem that
> > made me revert back to Access. I had the intranet running against
> > sql, everything in Access worked fine except for one major thing.
> > Very frustrating!
> >
> > There are two major tables that I use. Table1 holds address
> > information of our clients. The table's primary key is ID. Table2 is
> > the foreign key table to Table1 and it also has ID as the primary key.
> > Table2 holds the list of purchases that our clients made.
> >
> > I created a form in Access where the data enterer can enter and update
> > information on both tables. Since I created a relationship in Access,
> > I had no problems updating and creating new records within that form.
> > If we have a new client, we update Table1. If that client makes a
> > purchase we update Table2. So it doesn't necessarily mean that an
> > entry in table1 will require any data entering in table2. Here's
> > where the problem comes in when I upsize my database to SQL:
> >
> > The form will let me INSERT information to table1 but will not let me
> > INSERT information to table2. If I try to insert information into
> > table2 when using the form I created, it keeps bringing up this error
> > message telling me that there's a write conflict. However, I'm able
> > to update information within table2 with existing records that were
> > created prior to the conversion over to sql. I never had this problem
> > in Access as a standalone database. What am I doing wrong?
> >
> > There is some kind of problem with the relationship created between
> > these two tables. It won't even let me create a relationship in SQL
> > unless I uncheck the "check existing information on creation". I
> > know how to create joins in sql, so that's not the problem. It's
> > something with the foreign key constraints of some sort. Here's the
> > message that I get when I try to create the relationship without
> > uncheking the "check existing information on creation" check box when
> > I'm creating a diagram:
> >
> > Unable to create relationship 'FK_table2_table1'.
> > ADO error: ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
> > constraint 'FK_table2_table1'. The conflict occurred in database
> > 'CUSTOMERS', table 'Table1', column 'ID'.
> >
> > Please reply to this post if you have a solution. I would greatly
> > appreciate some help on this. Thanks.
|
|
|
|
|