Simple yet tricky and tough problem..

[dhruvslg86]

hii every one,
lets straight away ask my question..

i have three tables
table1
-----------------
id1 primary key <indentity column>
tb1
-----------------

table2
-----------------
id2 primary key <indentity column>
tb2
-----------------

table3
-----------------
id primary key <identity column>
id3 foreign key (referencing table1-->id1,table2-->id2)
-----------------
**table3 has two referential constraint for table1 and table2

now suppose i insert a tuple (say 'iop' in table1 and 'rey' in table2)
and then i want to insert the same tuples in table3......
THEN THE PROBLEM ARISES
when i insert 'iop' then the table2's referential constarint is violated and conflicts
and again
when i insert 'rey' then the table1's referential constarint is violated and conflicts

How do i basically implement a Many-to-one relationship in Microsoft Sql server 2005?

[b4web]

Table 1 primary key of 'iop' has been inserted.

Table 2 primary key of 'rey' has been inserted.

Table 3 primary key of 'you did not specify with 'ioprey' as the foreign key should work. Is that what you did?

[dhruvslg86]

the sql queries related to the problem is:

create table table1(id1 decimal identity(12,2),id11 varchar primary key(id1))

create table table2(id2 decimal identity(12,2),id22 varchar primary key(id2))

create table table3(id3 decimal identity(12,2),id33 decimal primary key(id3)
foreign key(id33) references table1,foreign key(id33) references table2)

insert into table1 values('i')
insert into table2 values('p')

insert into table3 values(14)

**the last line raises an error saying:
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__table3__id33__20C1E124". The conflict occurred in database "gh", table "dbo.table2", column 'id2'.
The statement has been terminated.

now suppose the value "14" had been present in both table 1 and table2
then it would have been succesfully inserted into table3

HOPE U GET THE PROBLEM

ALL OF THE ABOVE QUERIES HAVE WRITTEN IN RESPECT TO MICROSOFT SQL SERVER 2005

[chazzy]

then drop the constraint.

[criterion9]

Or in lou of dropping the constraint have 3 fields in table 3. One connecting table 1 PK and the other connecting to table 2 and the third of course being the PK.

[b4web]

I don't know the DBMS syntax, but I think this
"foreign key(id33) references table1,foreign key(id33) references table2)"
should be more like this
Foreign Key (id1x) references table1(id1)
Foreign Key (id2x) references table2(id2).

Also, the insert would be insert table3 values (12,'i','p');