Constraint or trigger, need help :-)
-
Hello everybody,
I have a problem that looks easy but it's not
Basically I have a table with 3 fields, the first one is a foreign key to another table let's call it F_K we'll call the other 2 fields A and B.
A is a nvarchar and can be anything, B is either true or false
I want that for each F_K I have only one true or none.
Example of a good table is the following:
F_K A B
01 gino true
01 mario false
01 ennio false
02 carla false
02 gina true
Example of a bad table is the following:
F_K A B
01 gino true
01 mario false
01 ennio true
02 carla false
02 gina true
this is bad because
select * from table where f_K = 01 and B="true"
will give me 2 records and I want to have either 1 or zero.
How can I implement this? Trigger? Constraint? What do you recommend?
If you have a quick solution to this would you please post it, it's driving me mad
Thanks in advance for your help,
Augusto
-
Augusto
I'm assuming from what you've said that column a is not unique.
There is two differnt ways of doing this both involving triggers depending on what you require. Basically do you require that if a new record is inserted, updated with a value of true for column B that any others that were true are set to false? Or do you wish to prevent updates, inserts if there is value of true associated with a foreign key already?
Let me know then i can make a suggestion
Dave
-
Hi Dave, thanks for your reply.
What I want is prevent updates, inserts if there is value of true associated with a foreign key already.
I managed to resolve the problem and for clarity I'll publish the script. Thanks again for your interest,
Augusto
USE
tempdb
GO
/* create a table */
CREATE TABLE
dbo.Test(
FK
int NOT NULL,
A nvarchar(10)
NOT NULL,
B
bit NOT NULL
)
GO
/* Define trigger dbo.trIU_Test */
CREATE TRIGGER
dbo.trIU_Test
ON
dbo.Test
FOR INSERT
, UPDATE
AS
IF
(
SELECT COUNT(*)
FROM (
/* Subquery for each FK i get the number
** of rows where B set to True
*/
SELECT T.FK, COUNT(*) Total
FROM dbo.Test T JOIN INSERTED I
ON T.FK = I.FK
AND T.B = I.B
WHERE T.B = 1
GROUP BY T.FK
) S
WHERE S.Total > 1
) > 0
BEGIN
/* I send an error message to the client and rollback the transaction */
RAISERROR ('B can be true only once for each value of FK', 16, 1)
ROLLBACK TRAN
END
GO
/* Test */
/* 1 - Insert one record at a time */
INSERT
dbo.Test VALUES(1, 'gino', 1)
INSERT
dbo.Test VALUES(1, 'mario', 0)
INSERT
dbo.Test VALUES(1, 'ennio', 0)
INSERT
dbo.Test VALUES(2, 'carla', 0)
INSERT
dbo.Test VALUES(2, 'gina', 1)
/* Error */
INSERT
dbo.Test VALUES(2, 'paola', 1)
GO
/* Output:
Server: Msg 50000, Level 16, State 1, Procedure trIU_Test, Line 23
B can be true only once for each value of FK
*/
/* 2 - If I insert a set of records with the same FK*/
INSERT
dbo.Test
SELECT
3, 'oreste', 0
UNION
SELECT
3, 'peppo', 0
UNION
SELECT
3, 'lello', 1
UNION
SELECT
3, 'beppe', 1 -- Error
GO
/* Output:
Server: Msg 50000, Level 16, State 1, Procedure trIU_Test, Line 23
B can be true only once for each value of FK
*/
/* 4 - If I insert a set di records with different FKs */
INSERT
dbo.Test
SELECT
3, 'oreste', 0
UNION
SELECT
3, 'peppo', 0
UNION
SELECT
4, 'lello', 1
UNION
SELECT
4, 'beppe', 1 -- Error
GO
/* Output:
Server: Msg 50000, Level 16, State 1, Procedure trIU_Test, Line 23
B can be true only once for each value of FK
*/
/* 4 - If I try to update a record... */
UPDATE
dbo.Test
SET
B = 1
WHERE
FK = 1
AND
A = 'mario'
GO
/* Output:
Server: Msg 50000, Level 16, State 1, Procedure trIU_Test, Line 26
B can be true only once for each value of FK
*/
/* If I try to update a set of records... */
UPDATE
dbo.Test
SET
B = 1
GO
/* Output:
Server: Msg 50000, Level 16, State 1, Procedure trIU_Test, Line 26
B can be true only once for each value of FK
*/
/* Delete table to clean DB */
DROP TABLE
dbo.Test
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply