> I want to add a row in a join table which i don't want to be > duplicated ever. > > So i have my tableID, table1ID, table2ID - if table1ID = 24 > and table2ID = 35 i don't want there to ever be a reoccurence of > them. I guess i have to set both of those fields (table1ID, table2ID) > to primary keys, but how can i do that? Do i do it in ms-sql or via > Coldfusion somehow?
As a bunch of people have already mentioned, you can create compound keys within your database. A compound key consists of more than one field. If you go that route, you really don't need the "tableID" field any more (assuming that field is currently being used as your primary key) - you can just make the combination of table1ID and table2ID your primary key. One disadvantage of this is that, if you have other tables which are dependent upon this one, those tables will need to contain both fields for use as a foreign key. However, in all likelihood you don't have any other tables which are dependent upon this one, but rather this table is dependent upon two other tables - this kind of table is often called a linking or intersection table. The "middle" table in a many-to-many relationship typically follows this pattern. Another disadvantage of this is that you won't be able to store historical relationship data - either a relationship exists now, or it doesn't. If you do want to store historical data, you'd need to keep the current "tableID" as your primary key, or add a third field to your two-field primary key - the value of that third field would have to be different for any two rows that have the same values for the other two fields. > If i do it via MS-SQL then won't it pop an error up in the code if > it happens? Yes, like any other invalid query you might run. It's up to you to catch and resolve those errors appropriately within your code. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211941 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
