December 3, 2007 at 3:04 pm
I have 3 tables. 1 table is the parent table that will create unique IDs. The other two tables will store these IDs in them with associated data. The problem is an ID from the parent table can only be stored in one of the child tables.
I was thinking of a Check Constraint that could call a function that would check to see if the ID is already in the other table.
Is there another way?
December 3, 2007 at 3:10 pm
it seems that what you are describing is literally the definition of a foreign key.
1.a child table can have the ParentId as a column, and a foreign key constraint in the child table guarantees a couple of things:
if the column is defined as NOT NULL: no data can can be entered without an explicit reference to the Parent Table's ParentID
3. It is nullable:data in the child table can be related to the Parent Table's ParentID
4. cannot delete the Parent Data without deleting it's child data first (or via a CASCADE DELETE)
see the books online about foreign key, or explain a bit more what you are trying to accomplish.
Lowell
December 3, 2007 at 3:17 pm
AVB (12/3/2007)
I have 3 tables. 1 table is the parent table that will create unique IDs. The other two tables will store these IDs in them with associated data. The problem is an ID from the parent table can only be stored in one of the child tables.I was thinking of a Check Constraint that could call a function that would check to see if the ID is already in the other table.
Is there another way?
Beyond what was said with a Foreign Key from the child to the parent you then use a trigger on each child to confirm the value does not exist in the other table. You can use RAISERROR to generate your own message.
December 4, 2007 at 1:04 am
Please, could you explain it in more detail? I'm not sure I understand it correctly... it might be, that you are trying to create a common ID for the two tables (meaning that you want to use the ID generated in parent table as ID - possible primary key - in the remaining two tables, not as a foreign key)? If not, it would be good to know why you want to use each ID in one of the tables only.
December 4, 2007 at 5:30 am
Thanks everyone for the replies.
I did create a PK/FK relationship between the two child tables and the Parent table. This PK/FK ID can only exist in only one of the two child tables (either one but not both at the same time).
I was wondering if SQL had some built in functionality that would accomplish this without using a trigger or a constraint (with a UDF). It's almost like a backwards key constraint. 🙂
December 4, 2007 at 7:08 am
AVB (12/4/2007)
Thanks everyone for the replies.I did create a PK/FK relationship between the two child tables and the Parent table. This PK/FK ID can only exist in only one of the two child tables (either one but not both at the same time).
I was wondering if SQL had some built in functionality that would accomplish this without using a trigger or a constraint (with a UDF). It's almost like a backwards key constraint. 🙂
Just a theory because I haven't tested but you could add a found in table column as part of the PK of the parent (1 and 2 as values) and in the FK version of it on the child you could place a constraint of 1 for the one table and 2 for the other so you would not be allowed to do. But don't have a moment to setup and you will need to handle certain decisions as the data loads to ensure you mark the correct value in the parent if the system will let you.
December 5, 2007 at 4:48 pm
Adding a "found in" column to the primary key of the master table wouldn't work -- key value 100 found in 1 and key value 100 found in 2 are unique so are valid values. It doesn't limit anything.
The only effective way is to use triggers which verify no incoming key values are in the "other" table:
if exists (select 1 from inserted i join OtherTable o on i.ID = o.id)
begin
--Error!
end;
An alternate method is to allow inserts and updates only through stored procedures which can do all the appropriate checking. While Insert procedures are fairly straightforward to write, update procedures can get pretty hairy really fast. But you have to prevent a key value from being modified.
Here is one solution that is a bit tricky but you don't have to worry about trying to limit column-level access so you only have to write an Insert procedure. It is based on the fact that an identity field, once a value has been written to it, can never be changed. So create all three tables with an identity primary key field. Here is the Insert procedure in pseudo-code:
insert new row into master table;
set @NewVal = @@Identity;
if @WhichTable = 1
turn on Identity Insert for table Child1;
insert values, including the @NewVal for the key value, into Child1;
turn off Identity Insert for table Child1;
else
turn on Identity Insert for table Child2;
insert values, including the @NewVal for the key value, into Child2;
turn off Identity Insert for table Child2;
the end;Now you can allow uncontrolled update access to the child tables because their key values can never be changed. You must, of course, limit access to the master table -- wouldn't want anyone truncating it. 😀
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
December 6, 2007 at 8:23 am
Thanks for the reply.
We do control the inserts by SP but at a table level we'll have better control so I added a check constraint to the two child tables. The check constraint calls a UDF and checks for the ID in the other table. If the ID exists the insert fails.
December 6, 2007 at 8:59 am
I don't know if this will help at all, but here goes:
If you track in the parent table which "table" that particular key exists in, you can get most of the way there. Meaning
Parent table:
ID CHILD
100 A
101 A
102 B
....etc
You then force ID to be unique, and you use ID+child as your COMPOUND PK. At that point, the FK constraint itself regulates the insert DRI, and not some additional function.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 6, 2007 at 9:10 am
Matt,
That's not a bad idea either. I'll have to try that too. Thanks.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply