I've got a table that is supposed to track days and costs for shipping product from one vendor to another. We (brilliantly :p) stored both the shipping vendors (FedEx, UPS) with the product handling vendors (Think... Dunder Mifflin) in a "VENDOR" table. So, I have three columns in my SHIPPING_DETAILS table that all reference VENDOR.no. F...
As far as I know, foreign keys (FK) are used to aid the programmer to manipulate data in the correct way. Suppose a programmer is actually doing this in the right manner already, then do we really need the concept of foreign keys? Are there any other uses for foreign keys? Am I missing something here? ...
There are several types of objects in a system, and each has it's own table in the database. A user should be able to comment on any of them. How would you design the comments table(s)? I can think of a few options: One comments table, with a FK column for each object type (ObjectAID, ObjectBID, etc) Several comments tables, one for ea...
When setting up foreign keys in SQL Server, under what circumstances should you have it cascade on delete or update, and what is the reasoning behind it? This probably applies to other databases as well. I'm looking most of all for concrete examples of each scenario, preferably from someone who has used them successfully. ...
Are disabling and enabling FK constraints supported in SQL Server? Or is my only option to 'drop and then re-'create' the constraints? ...
I use FB1.5.3 and need to eventually add some FK to the database. It happens that if I try to do it while anyone else is connected to the DB I get an exception. Is there a way to add an FK while the system is running? ...
I'm working on an upgrade for an existing database that was designed without any of the code to implement the design being considered. Now I've hit a brick wall in terms of implementing the database design in code. I'm certain whether its a problem with the design of the database or if I'm simply not seeing the correct solution on how to...
Is it possible to create in a table of database a foreign key to a column of table in another database in SQL 2005 ? ...
I'm primarily interested in pgsql for this, but I was wondering if there is a way in any RDBMS to do an insert operation, without disabling and re-enabling any FOREIGN KEY or NOT NULL constraints, on two tables that refer to each other. (You might think of this as a chicken that was somehow born from its own egg.) For a practical examp...
I'm just getting started working with foreign keys for the first time and I'm wondering if there's a standard naming scheme to use for them? Given these tables: task (id, userid, title) note (id, taskid, userid, note); user (id, name) Where Tasks have Notes, Tasks are owned by Users, and Users author Notes. How would the three forei...
In MySQL, how do I get a list of all foreign key constraints pointing to a particular table? a particular column? This is the same thing as this Oracle question, but for MySQL. ...
Hello! I was wondering, is there any possibility to create a table without a primary key, but with two foreign keys, where the foreign keys pairs are always different? For example, a STOCK table with item_id and warehouse_id as foreign keys from ITEMS and WAREHOUSES tables. So same item can be in different warehouses. The view of the tab...
A couple of recent questions discuss strategies for naming columns, and I was rather surprised to discover the concept of embedding the notion of foreign and primary keys in column names. That is select t1.col_a, t1.col_b, t2.col_z from t1 inner join t2 on t1.id_foo_pk = t2.id_foo_fk I have to confess I have never worked on any databa...
Can anyone provide a clear explanation / example of what these functions do, and when it's appropriate to use them? ...
I have been slowly learning SQL the last few weeks. I've picked up all of the relational algebra and the basics of how relational databases work. What I'm trying to do now is learn how it's implemented. A stumbling block I've come across in this, is foreign keys in MySQL. I can't seem to find much about the other than that they exist in...
For example to create a hierarchy of categories you use a column 'parent_id', which points to another category in the same table. Should this be a foreign key? What would the dis/advantages be? ...
Hi, Is there a way to persist an enum to the DB using NHibernate? That is have a table of both the code and the name of each value in the enum. I want to keep the enum without an entity, but still have a foreign key (the int representation of the enum) from all other referencing entities to the enum's table. ...
SQL Server 2005. I'm adding Foreign Key constraints to the database of an application that allegedly didn't need them. Naturally, the data has become unreliable and there are orphaned entries in the foreign key field. Setup: Two tables, TableUser and TableOrder. TableUser has Primary Key 'UserID', and TableOrder has Foreign Key 'UserI...
For better or worse, we have a solution that relies on multiple databases that all reference a common administration database. Databases ship as part of modules, and not all modules are required for an installation (probably why we have multiple databases in the first place). The admin database is required, however ... so it will alway...
I've got a table structure I'm not really certain of how to create the best way. Basically I have two tables, tblSystemItems and tblClientItems. I have a third table that has a column that references an 'Item'. The problem is, this column needs to reference either a system item or a client item - it does not matter which. System items h...