foreign-keys

assigning data in a new table to an existing foreign key in a for loop

Hi there, I was wondering if there was a way to assign new data in a table to an existing foreign key. For example if i use the following loop to assign randomly generated numbers to columns in the customer table, how would I be able to link cust_id, which I have assigned as a foreign key in the Sales table, with new data created each ti...

Is it possible to set a unique constraint as a foreign key in another table?

Is it possible to set a unique constraint as a foreign key in another table? If yes, how would you go about declaring it? How would you go about assigning a candidate key? Is it possible? Sorry, I'm a real noob at oracle. Example: I have a product table that consists of: prod_id, prod_name, prod_price, QOH Where I want prod_name to ...

Is it possible to drop all foreign key constraints on a table at once in mySQL 5?

In mySQL 5, is there a way to drop all foreign key constraints on a table with one SQL statement without referring to them by name? I'm writing a DB update script, and unfortunately some of the sites had constraints created with "wrong" names. I'm trying to avoid going in and getting the actual constraint names from the DB and insertin...

Struggling to map a relationship with NHibernate

I'm working with 2 tables, Employees and a table called EmployeeGroups as follows. I've left out most of the unrelevant columns below. I'm struggling to map the foreign key relationship. Its a one-to-one relationship where not every EmployeeId in the Employees table will exist in the EmployeeGroups table. I'm wondering how to setup the E...

Composite Key on a Self Referencing Table

We have a composite primary key for the site table defined below. Functionally, this does exactly as we would like it to. Each site should have a parent site of the same district. Defining the table in this way allows specifically for that. CREATE TABLE [dbo].[site]( [site_number] [nvarchar](50) NOT NULL, [district_id] [bigint] ...

How to add composite primary key to table

create table d(id numeric(1), code varchar(2)) After I create the above table how can I add a composite primary key on both fields and also a foreign key? ...

How to add not null in table field

create table re(id numeric(1),bin varchar(10)) how to add not null on table as well as foreign key ...

Get Foreign Key Value

How can I get the foreign key values? I have a common vehicle model that links to the year, series, engine type, body style, transmission and drive train...all as foreign keys. I'd like to get the values of these fields for my app, but I'm stuck as to how I'd go about them. Any ideas will be highly appreciated. class Model(models.Mod...

Sorting Records using Foreign Keys

From my question at http://stackoverflow.com/questions/1362426/get-foreign-key-value, I managed to get the desired output...only one last bit remains. I want to sort my records by the year, make, then model in that order. I thought it'd be as simple as Vehicle.objects.all().order_by('common_vehicle') but this doesn't sort anything. ...

Grails - multiple belongsTo of same class with cascading deletion

Hi all, This one is for the Grails users here. I asked it on the grails - user mailing list, but I figured since I've been fighting this for a couple of days I should cast as wide a net as possible. I'm having some difficulty with trying to model relationships between two objects of the same type in another object (different type) ref...

Foreign key constraints while dumping data

mysqldump --compact --no-create-info -h192.168.150.180 -uroot -p live pnlbus_stops | sed s/pnlbus_stops/bus_stops/g | mysql test I am getting an error: ERROR 1062 (23000) at line 1: Duplicate entry 'AN' for key 1 This is because bus_stops table in the test DB has foreign key constraints. How do I truncate the bus_stops table from test...

Merging identical tables but maintaining separate referential integrity

Consider a dimensional model with fact tables like (fk_dim1value, fk_dim2value, ..., value) where the fk_X columns are foreign keys into corresponding trivial dimension tables dim1value (id, value), dim2value (id, value), etc. These fact-and-dimension tables are collected automatically from disparate sources, so there's a lot of them .....

django: manually adding a foreign key column (newcolumn_id_refs_id_4bfb2ece ?)

Hi there, I need to add a foreign key field to an existing django model/postgres table. As per the django documentation, I ran the 'sqlall myapp' command to 'work out the difference'. The obvious difference is that the table in question now has an extra column with a new contraint, which looks like this: ALTER TABLE "myapp_mytable" A...

How can I find unindexed foreign keys in SQL Server

I have a SQL Server 2000 database with approximately 220 tables. These tables have a number foreign key relationships between them. Through performance analysis, we've discovered a number of these foreign key relationships are missing indexes. Instead of being reactive to performance problems, I'd like to be pro-active and find all fo...

SubSonic 3 ActiveRecord migration ignores foreign keys

Using SubSonic 3 ActiveRecord, I generated code from an existing database that had foreign keys. To ensure database schema is always correct when switching databases, I placed migration code at the beginning of the app, using IDataProvider.MigrateToDatabase<MyClass>() for each class generated by ActiveRecord.tt. Turns out, migration code...

LinqToSql is trying to insert a foriegn key child when it already exists over WCF. What can I do?

Hi guys, I'm having trouble inserting a new LinqToSql object over WCF. What I'm doing is just sending an Orders Batch to the service. Inside the batch are Orders that have already been sent previously. When I do data.batches.InsertOnSubmit(newbatch) I get a sql error: "Violation of PRIMARY KEY constraint 'PK_HTOrder'. Cannot inse...

Any support for foreign keys in CakePHP schema shell or migrations?

I'm new to CakePHP, and I'm trying to decide between the schema shell that comes with CakePHP and the migrations tools that were written by Joel Moss and Georgi Momchilov. So far I haven't found any direct support in either of them for creating foreign key constraints. It appears that you might be able to write raw SQL in a migration to...

[Linq to SQL] Multiple foreign keys to the same table

I have a reference table with all sorts of controlled value lookup data for gender, address type, contact type, etc. Many tables have multiple foreign keys to this reference table I also have many-to-many association tables that have two foreign keys to the same table. Unfortunately, when these tables are pulled into a Linq model and th...

MySQL Creating tables with Foreign Keys giving errno: 150

I am trying to create a table in MySQL with two foreign keys, which reference the primary keys in 2 other tables, but I am getting an errno: 150 error and it will not create the table. Here is the SQL for all 3 tables: CREATE TABLE role_groups ( `role_group_id` int(11) NOT NULL `AUTO_INCREMENT`, `name` varchar(20), `description` ...

Are MySQL Foreign Key auto-generated names deterministic?

When constraints are created they are given names that look something like this 'FK5E6B788655A1514E'. I'm wondering if the name generation is deterministic or random. I noticed that two separate databases I was using, same schemas, ended up with the same FK names. Does it make sense when writing an upgrade script from one version of a ...