Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-07-27 : 10:16:46
|
| Bauji writes "I have set relationship between various table as followed:Contact ContactID smallint (Primary Key)ContactName varchar(20)-------------------OtherContactOContactID smallint (Primary Key)OContactName varchar(20)---------------------AttendanceAttendeeID smallintAttendanceType char(1) ('C' for Contact, 'O' for Othercontact)Now I sent refrential integrity as followed:ALTER TABLE Attendance ADDCONSTRAINT FK_ContactAttendance FOREIGN KEY (AttendeeID) REFERENCES dbo.Contact(ContactId) On UPDATE CASCADE On DELETE NO ACTION, Constraint CK_ContactAttendance Check (AttendeeType='C')ALTER TABLE Attendance ADDCONSTRAINT FK_OtherContactAttendance FOREIGN KEY (AttendeeID) REFERENCES dbo.OtherContact(OContactId) On UPDATE CASCADE On DELETE NO ACTION, Constraint CK_OtherContactAttendance Check (AttendeeType='O')Now problem is when ever I add a attendance, it gives me error Error Type:Microsoft OLE DB Provider for SQL Server (0x80040E2F)DELETE statement conflicted with COLUMN REFERENCE constraint'FK_ContactAttendance'.The conflict occurred in database 'Personal', table 'Attendance', column 'AttendeeID'.In that case there is a record in contact table where there contactid value is '23' and a record in other contact table there ocontactid value is '39'. If I set both value to '23', it wont gives error.What I should do to make it work? I tried various option, but not able to work it properly.Let me know how should I handle it.I have One more question that isIs there any way to set foreign key conditionally.?" |
|
|
|
|
|
Refrential Integrity with Constraint