SQL Database Table
CREATE TABLE [dbo].[MyTable](
[GA] [uniqueidentifier] NOT NULL,
[GB] [uniqueidentifier] NOT NULL,
[TypeId] [int] NOT NULL CONSTRAINT [DF_MyTable_TypeId] DEFAULT (1),
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[GA] ASC,
[GB] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
We have the above table. Our primary keys do not allow us to insert duplicates. However, we also do not want to allow duplicates if the values were reversed.
For instance, our table contains the following row.
GA = 111, GB = 222
We do not want to insert a new row with the following values:
GA = 111, GB = 222
Or
GA = 222, GB = 111
Can we add a unique index to handle this? And prevent this on the database end?
[GA] [uniqueidentifier] NOT NULL,
[GB] [uniqueidentifier] NOT NULL,
[TypeId] [int] NOT NULL CONSTRAINT [DF_MyTable_TypeId] DEFAULT (1),
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
(
[GA] ASC,
[GB] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
We have the above table. Our primary keys do not allow us to insert duplicates. However, we also do not want to allow duplicates if the values were reversed.
For instance, our table contains the following row.
GA = 111, GB = 222
We do not want to insert a new row with the following values:
GA = 111, GB = 222
Or
GA = 222, GB = 111
Can we add a unique index to handle this? And prevent this on the database end?
ASKER
Yes that would help, I am good at delete triggers but inset/update triggers I need help with
try this,
CREATE TRIGGER duplicates
ON MyTable
AFTER INSERT, UPDATE
AS
SELECT COUNT(*) From MyTable WHERE GA = (SELECT INSERTED.GB FROM INSERTED)
OR GB = (SELECT INSERTED.GA FROM INSERTED);
Rollback;
GO
CREATE TRIGGER duplicates
ON MyTable
AFTER INSERT, UPDATE
AS
SELECT COUNT(*) From MyTable WHERE GA = (SELECT INSERTED.GB FROM INSERTED)
OR GB = (SELECT INSERTED.GA FROM INSERTED);
Rollback;
GO
If count > 0 then rollback
UNIQUE index will only work with existing data in the table. You can only use that to handle the first case.
To handle the second (or reverse) case, you'll need a trigger on INSERT and UPDATE that checks for the existence of the pair and rolls back if it exists already.
To handle the second (or reverse) case, you'll need a trigger on INSERT and UPDATE that checks for the existence of the pair and rolls back if it exists already.
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks that worked great!
ASKER
This did not allow me to insert:
Table contains:
B7, B6
F8, C7
Record to insert
EC, C7
The trigger raised an error.
Table contains:
B7, B6
F8, C7
Record to insert
EC, C7
The trigger raised an error.
you will need a trigger to check if the "reversed" item already exists.
do you need help with creating such trigger(s)`?