Creating Referential Integrity for SQL Server 2005 DB?

[blakemckenna]

For the first time, I am trying to create referential integrity on a database and having difficult doing it. Obviously I want data in one table to change only if data in another table changes according to the PK's or FK's. I'm having trouble doing this using the FK's. I keep getting the message "The Columns in tblActions do not match an existing Primary Key or unique constraint". What is this message. I was trying to create a FK relationship between 2 tables.

Thanks,

[jmcilhinney]

What are the two tables your trying to relate? What are the names and types of the columns you're trying to relate? Do they currently contain any data?

If your parent table has a composite key then the foreign key in the child table must be composite too.

[szlamany]

Here's a simple script I dug up that creates a table with a primary key constraint and then alters that table to add two foreign key constraints.

We like to script all this so that the names of the PK and FK follow a pattern.

Drop Table Algorithm_T
Go
CREATE TABLE Algorithm_T
(Yr             int             NOT NULL
,Bldg           tinyint         NOT NULL
,StuId          int             NOT NULL
,RandomOrder	real		NULL
,StartTime	datetime	NULL
,EndTime	datetime	NULL
,Tries		int		NULL
,NonSched	int		NULL 
,TDate          datetime        NULL
,constraint     PKAlgorithm
                PRIMARY KEY CLUSTERED (Yr, Bldg, StuId)
)
GO

ALTER TABLE Algorithm_T
        ADD CONSTRAINT FKAlgorithmStudent
        FOREIGN KEY (StuId) REFERENCES Student_T(StuId)
GO
ALTER TABLE Algorithm_T
        ADD CONSTRAINT FKAlgorithmSchoolYr
        FOREIGN KEY (Bldg,Yr) REFERENCES SchoolYr_T(Bldg,Yr)
GO

Having FKAlgorithmSchoolYr as the FK name tells us the two tables the FK relates so that when we pop an error out of MS SQL it's more descriptive of the actual problem.

One FK has a single field (StuId) that must exist in STUDENT_T.

The other FK has two fields (Bldg,Yr) that must exist in combination in the SCHOOLYR_T table.

Are you looking to CASCADE update's upon these columns as well?

[blakemckenna]

JMC,

The 2 tables names I am try to relate are tblGRoups and tblActions. I need to be able to delete from the "tblGroups" and automatically delete from the "tblActions" when this happens. The PK for "tblGroups" is "groupID"/Integer. The PK for "tblActions" is "actionTypeID" and "groupID", both Integers. They both have a "groupName" field in them. This was the field that I was trying to associate but it was giving me that error msg.

[szlamany]

That would be CASCADE DELETE - just a syntax option on the FOREIGN KEY creation.

Do you have access to BOOKS ONLINE?

[blakemckenna]

szlamany,

What I'm trying to do is whenever the "tblGroups" gets updated (specifically the "groupName"), I wish for the "groupName" field in the "tblActions" to be updated as well. What do I need to do?

Thanks,

[FunkyDexter]

They both have a "groupName" field in them. This was the field that I was trying to associate

That's your problem right there. Group Name doesn't have a unique constraint against it and you can only set up a foreign key to a unique column. Primary Keys are, by definition, unique and are usually the column you'd link to. Is there any reason your not linking on the groupID column?

[FunkyDexter]

whenever the "tblGroups" gets updated (specifically the "groupName"), I wish for the "groupName" field in the "tblActions" to be updated as well

Ah, I think you may have missunderstood the way databases are supposed to work in this regard. You would normally only store GroupName on the Group table, NOT on the Actions table. When you want to read the group name for an action you first go and get the Group record for that Action (by grabbing the one with the apropriate Group ID). Then you read the Group Name from there. That way you're always reading the most up to date Group Name for a given Action.

This isn't actually a function of referential integirty, rather it's third normal form. I'd really recommend reading up on Third Normal form as its at the absolute core of all DB design.

[blakemckenna]

I am linking on the "groupID" column, however, whenever I change the "groupName" field in the "tblGroups"...the "groupName" field in the "tblActions" doesn't change...

[szlamany]

Why do you have GroupName in two tables?

Don't both of these tables have GroupId in them??

[blakemckenna]

Szlamany,

Your right, they do have both fields defined in them. What if I removed the "groupName" from the "tblActions"...would that make any difference as far as changing data in the "tblGroups"?

[szlamany]

GroupId defines GroupName - right? For instance - GroupId 1 might be "Red Group", for example.

What table stores this simple relationship - two columns - GroupId and GroupName. And of course GroupId would be unique in this table - only appearing once in a single row.

[FunkyDexter]

What if I removed the "groupName" from the "tblActions"...would that make any difference as far as changing data in the "tblGroups"?

That's exactly what you should do. In OO terms, a group should 'know' what its name is. An Action should 'know' what group it belongs to but it doesn't need to 'know' what the name of that group is. If you want to find the name you interrogate the Group record, not the Action record.

[blakemckenna]

So what are you suggesting exactly?

[techgnome]

The recommendation is to remove GroupName from the Action table.... it's duplicated data.... you *know* that Action A belongs to "Red Group" because the GroupID in the Action table relates to the GroupID of "Red Group" in the Group table.... So if you need to update the Group Name to "Green Group" ... you just update tblGroup....

-tg

[FunkyDexter]

As TG says, the data should exist in one place and one place only. At the moment you've put the group name in the action table - but you don't need it to be there. Sorry, but this is quite hard to explain if you haven't grasped third normal form (which is why I suggested that a quick bone up on that should be your first step) but I'm trying to make it as clear as possible.

Perhaps a sample query will help. Say you want to know what group action A belong to. Your query would read:-
Select GroupName
From Groups
Inner Join Actions On Groups.GroupID = Actions.GroupID
Where ActionName = 'Action A'

or to put it another way

Select GroupName
From Groups
Where GroupID =
(Select GroupID
From Actions
Where ActionName = 'Action A')

(The first of these forms is better practice but you might find the second more readable)

In neither of those queries did I need to interrogate GroupName on the Action Record. Instead, I've found the correct Group Record for the Action I'm interested in and have interrogated GroupName on the Group Record.

This way you don't have to worry about updating GroupName on the Action records - instead you just update it on the Group record. Because your queries read from the group record, updating the name on that group record will, in effect, change the group name for all actions within that group. You can then remove the groupname field from the actions record entirely, it's now redundant and you don't need to bother about maintaining it.

Does that make it any clearer?

[blakemckenna]

Ok, I've done this but I don't see any udpates being made to the subordinate tables when I update the "groupName" in the "tblGroup". Shouldn't "groupName" in the "tblActions" get changed as well? Do I need to do any special coding in my VB.App?

[FunkyDexter]

GroupName should not be in the Actions table. Remove it. You never read from it so you don't need to maintain it.


edit> oooops I accidentally left the word 'not' out of the above post... which was a bit critical to it's meaning really.

[blakemckenna]

OK,

It seems to be working now, but all updates to subsequent tables don't happen until I exit the program and re-enter it. Is there anything I can do to reflect the changes immediately?

Thanks,

[szlamany]

Groupname should only be in a single table.

That table should have two columns - GroupId and GroupName.

I don't think you are appreciating this fully.

[techgnome]

No, I think he does have it now.... now he needs to get his DataTable to refresh itself to reflect datachanges.... nothing major there.... Simply clear it, then re .Fill it....

-tg

[FunkyDexter]

As Steve says, if you have a field called GroupName on ANY other table in your database, remove it. The ONLY place that field should exist is in the Group Table.

[blakemckenna]

Clear what and refill what? Can I do a DB.Refresh?

[FunkyDexter]

That all depends on how you've been retreiving and displaying the data in the first place. Have you been doing it in code? If so post your code here. Have you been databinding to datatables or views? If so post all the details. Basically, give us as much info as possible and we should be able to point you in the right direction.