April 24, 2002 at 8:31 am
I'm using an access front end to sql tables. I have a look up field to select a value which would a the foreign key to the table. My problem is that I want the foreign key to be unique (one to one relationship) but there are instances that the field may be null on more than one record. I cannot assign the field a unique index because of the null values, therefore I was wondering if I could add a constraint to allow blank fields but no duplicate data entries. If so, can someone lead me in the right direction as this is all new to me.
Thanks!
Judy
April 24, 2002 at 1:02 pm
Trigger would work best like so. On you table where the null can be allowed or null do something similar to the following.
CREATE TRIGGER tr_PreventDupNotNull ON dbo.tblMain
FOR INSERT, UPDATE
AS
IF EXISTS (SELECT FKCOLNAME FROM tbl1 RIGHT JOIN tblMain ON tbl1.FKCOLNAME = tblMain.FKCOLNAME WHERE FKCOLNAME IN (SELECT col FROM inserted))
BEGIN
RAISERROR('Values is not unique.',16,-1)
RETURN
END
This should do the trick as long as I have the table thoughts right.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 24, 2002 at 3:24 pm
Since it's not defined as an INSTEAD OF trigger (not available prior to SQL 2000), that means the data operation has occurred. As a result, a ROLLBACK TRAN needs to be executed as well. If no transaction has been explicitly defined, SQL Server has created an implicit one so that the trigger can roll back the data operation.
Keep in mind, this will roll back the entire set of changes, meaning if one row out of ten fails, all will be rolled back. If you want it to check row by row, then you're going to have build a cursor to check and correct (not rollback) and take the potentially serious performance hit as a result.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
April 24, 2002 at 4:36 pm
Hey Brian I don't believe Access commits in blocks anyway, they are one at a processes to the database. So I don't think it will be an issue however the ROLLBACK statement is right, sorry about that.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 24, 2002 at 4:40 pm
Would depend on whether they submitted as a transaction or not I think. Been meaning to look at Access to see how it does this, maybe tomorrow should free time appear.
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply