Restrict Values to Values occurring in other table
-
Hi Guys, I've had a look around the web and around this forum in particular but can't seem to find what I'm looking for, hope you can help.
I'm designing a DB to track info related to company PDAs (serial num, model num, sim number, etc...) and I need to be able to restrict entries in my tables to those that exist in other tables OR to be NULL.
Basically a device can exist without a Sim card, or have a sim from the sim table. No two devices can have the same sim.
I thought a foreign key was what I'm looking for but I get errors when I try to have more than one NULL
Here's a simplification of my tables
DEVICE TABLE
PK | Serial | Model | Sim ID
---------------------------------
10 | 01234| NokiaB | 10
20 | 65465| HTC-A | 20
30 | 98556| Palm-C | NULL
40 | 55045| Nokia-B| NULL
Sim Table
PK | fonenum | Sim serial | Tariff
---------------------------------
10 | 0055123 | 123456 | Voice+Data
20 | 0055321 | 123457 | Data only
Is it possible to restrict entries in this way? Do I need a foreign key and just haven't set it up right?
FYI: Server is MS SQL 2000 and my client is Server Management Studio 2008
Any help would be greatfully recieved, even if it's just to say I'm completely barking up the wrong tree
Thanks
-
Hello,
Thanks for the sample data. It was great for testing.
Columnnames can vary.
I would implement it via a foreign key (validation) +trigger (preventing duplicates)
--check if sim is valid using a foreign key
foreignkey fk_device sim
master table Sim Table
parent table DEVICE TABLE
non-unique index on device.simid (to speed up foreign key)
--Triggers to rollback transaction on duplicate sims, 1 for insert, 1 for update
--inserted contains new data
--deleted contains old data
CREATE TRIGGER dbo.TR_DEVICES_INSERT ON [dbo].[devices]
FOR INSERT
AS
SET NOCOUNT ON
--sim shouldn't be in devices
--nulls=nulls is false
--if we find one of the new sims (inserted) in our table, rollback
if exists
(select inserted.simid from inserted
inner join dbo.devices devices
on inserted.simid=devices.simid
and inserted.simid is not null
)
BEGIN
RAISERROR ('Sim already taken', 16, 1)
ROLLBACK TRANSACTION
END
--Triggers to rollback transaction on duplicate sims, 1 for insert, 1 for update
CREATE TRIGGER dbo.TR_DEVICES_UPDATE ON [dbo].[devices]
FOR update
AS
SET NOCOUNT ON
--no duplicate sims in inserted
if exists
(select simid from inserted
group by simid
having count(*)>1
)
BEGIN
RAISERROR ('Duplicate sim in inserted', 16, 1)
ROLLBACK TRANSACTION
END
--second check
--only accept an existing sim when it changes device (different pk)
--testdata
--existing pk 10, sim 10
--existing pk 20, sim 20
--existing pk 30, sim 30
--update pk 30->sim 10 fails because inserted.pk(30)<>devices.pk(10)
--update pk10->sim 20+pk20->sim 10 is succesfull (swapped, separate fails)
--example combined update
/*update devices
set simid=sim
--select *
from devices
inner join
(select 20 as pk,10 as sim
union all
select 10 as pk,20 as sim
union all
select 30 as pk,30 as sim
) A
on a.pk=devices.pk
*/
if exists(
select inserted.simid from inserted
inner join dbo.devices devices
on inserted.simid=devices.simid
and inserted.pk<>devices.pk
)
BEGIN
RAISERROR ('Duplicate sim in update', 16, 1)
ROLLBACK TRANSACTION
END
Should work.
-
Jo, Thank you so much! That is exactly what I was wanting to acheive. Thank you so very much!
I've switched the real table and field names into the script you gave me and it works a charm. I want to make the error messages a little more helpful by specifying which other serial number is using the SIM but I think I can work this out for myself.
Thanks again
There was just one little bit of your response I didn't quite grasp tho
Jo Pattyn (12/3/2008)
non-unique index on device.simid (to speed up foreign key)What does this entail? i had a poke around in the indexes part of the object explorer and "New Index" is greyed out. the below primary key is listed there.
My apologies if this is not the best way to post this info in the forum, if it's not then a little advice on how to post SQL Scripts would be appreciated
USE [BV-LEMSHIP]GO/****** Object: Index [PK_tblDevice_1] Script Date: 12/04/2008 16:22:21 ******/ALTER TABLE [dbo].[tblDevice] ADD CONSTRAINT [PK_tblDevice_1] PRIMARY KEY CLUSTERED([DeviceSerialNumber] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]GOOnce again thanks a million for your help
JB
-
Cheers!
About the non-clustered index on device.simid.
By default, when you create a foreign key, sql server doesn't automatically add an index on both columns involved in the master-parent relationship (at least not in sql server 2000)
You already have a primary key (comes with an index) on sim.pk. The other side, device.simid, should also have an index (thus preventing table scans when checking the foreign-key constraint).
ps, just noticed that the insert-trigger also needs a duplicate check at the records from the inserted table (like the first check in the update trigger).
-
Jo Pattyn (12/4/2008)
About the non-clustered index on device.simid.By default, when you create a foreign key, sql server doesn't automatically add an index on both columns involved in the master-parent relationship (at least not in sql server 2000)
Understood, thank you.
Jo Pattyn (12/4/2008)
ps, just noticed that the insert-trigger also needs a duplicate check at the records from the inserted table (like the first check in the update trigger).Yeah I noticed that and added it in, you were just checking I was paying attention really weren't you
I didn't get it to allow me to swap Sims in a single transaction tho. it would appear that the example in your comments...
--update pk10->sim 20+pk20->sim 10 is succesfull (swapped, separate fails)
causes the trigger to fire anyway.
I've been tearin my hair out trying to think of a way to acheive this (to be honest I hadn't thought of it before you mentioned it).
The best idea I can come up with would be (please remember I've not developed anything in SQL before!):
1) within the trigger, assemble list of changed records which clash with exiting records
2) use some sort of iterative loop to check if simid of each conflicting sim is changing anyway in which case allow the update
Is this sort of loop possible?
-
Back again.
What works:
Allows: swapping in a single update
Disallows: swapping in 2 separate updates
Sample data devices:
pk 10 , sim 10
pk 20 , sim 20
Allowed:
update devices
set simid=sim
from devices
inner join
(
--setting multiple values in 1 update
select 20 as pk,10 as sim
union all
select 10 as pk,20 as sim
) A
on a.pk=devices.pk
Result:
pk 10 , sim 20
pk 20 , sim 10
Disallowed:
begin transaction
update devices
set simid=10
where pk=20
update devices
set simid=20
where pk=10
commit transaction
Changed the insert trigger to match the update trigger.
CREATE TRIGGER dbo.TR_DEVICES ON [dbo].[devices]
FOR INSERT,UPDATE
AS
SET NOCOUNT ON
DECLARE @duplicatesim int
SET @duplicatesim=NULL
--no duplicate sims in inserted
if exists
(
select simid from inserted
group by simid
having count(*)>1
)
BEGIN
--feedback
SELECT TOP 1 @duplicatesim=simid from inserted
group by simid
having count(*)>1
RAISERROR ('Duplicate sim %d in insert detected', 16, 1,@duplicatesim)
ROLLBACK TRANSACTION
END
--second check
--only accept an existing sim when it changes device (different pk)
--testdata
--existing pk 10, sim 10
--existing pk 20, sim 20
--existing pk 30, sim 30
--update pk 30->sim 10 fails because inserted.pk(30)<>devices.pk(10)
--update pk10->sim 20+pk20->sim 10 is succesfull (swapped, separate fails)
--example combined update
/*update devices
set simid=sim
--select *
from devices
inner join
(select 20 as pk,10 as sim
union all
select 10 as pk,20 as sim
union all
select 30 as pk,30 as sim
) A
on a.pk=devices.pk
*/
if exists(
select inserted.simid from inserted
inner join dbo.devices devices
on inserted.simid=devices.simid
and inserted.pk<>devices.pk
)
BEGIN
--feedback
SELECT top 1 @duplicatesim=inserted.simid from inserted
inner join dbo.devices devices
on inserted.simid=devices.simid
and inserted.simid is not null
and inserted.pk<>devices.pk
RAISERROR ('Duplicate sim %d in update', 16, 1,@duplicatesim)
ROLLBACK TRANSACTION
END
-
Jo Pattyn (12/9/2008)
Back again.What works:
Allows: swapping in a single update
Disallows: swapping in 2 separate updates
...
ROLLBACK TRANSACTION
END
Jo, you're an absolute gent (unless of course you're a lady)
Thanks for all your help, I'll have a play with this later.
I really appreciate it.
JB
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply