Simple yet tricky and tough problem..
hii every one,
lets straight away ask my question..
i have three tables
id1 primary key <indentity column>
id2 primary key <indentity column>
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
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?
Seems okay to me
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?
let me get more clearer
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
then drop the constraint.
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.
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');
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread