foreign-keys

Django ForeignKey created empty?

This seems very basic and I must be missing something, but here goes anyways... With two models like so: class School(models.Model): name = models.CharField("Official School Name", max_length=128) address = models.TextField("Address of the School", max_length=256) mascot = models.CharField("Name of the School Mascot", m...

efficiently trimming postgresql tables

I have about 10 tables with over 2 million records and one with 30 million. I would like to efficiently remove older data from each of these tables. My general algorithm is: create a temp table for each large table and populate it with newer data truncate the original tables copy tmp data back to original tables using: "insert into o...

Fluent NHibernate - Set reference key columns to null

Hi, I have a table of Appointments and a table of AppointmentOutcomes. On my Appointments table I have an OutcomeID field which has a foreign key to AppointmentOutcomes. My Fluent NHibernate mappings look as follows; Table("Appointments"); Not.LazyLoad(); Id(c => c.ID).GeneratedBy.Assigned(); Map(c => ...

Merge replication server side foreign key violation from unpublished table

We are using SQL Server 2005 Merge Replication with SQL CE 3.5 clients. We are using partitions with filtering for the separate subscriptions, and nHibernate for the ORM mapping. There is automatic ID range management from SQL Server for the subscriptions. We have a table, Item, and a table with a foreign key to Item - ItemHistory. Bot...

How to design this simple database?

I have 2 tables - one storing user information (id, username, password) and the second one storing information about events (id, name, description, date, username(represents the user who created the event)). I would like to implement 'favourite events' functionality. This would allow the user to store his favourite events and later displ...

View all foreign key constraints for entire MySQL database

I have a large database with over 150 tables that I've recently been handed. I'm just wondering if there is an easy way to view all foreign key constraints for the entire DB instead of on a per-table basis. ...

TDD with HSQLDB -- removing foreign keys

I'm using HSQLDB for data layer integration testing, which is great. However, I'm finding that my foreign key constraints are getting in the way of my tests. For example, to test a simple select on one table, I have to insert dummy data into five additional tables. This makes me want to throw things. I have JPA annotations throughout...

Mapping multiple foreign keys to one table

Hi I'm using Fluent Nhibernate, i need to create mappings for 2 tables: Simplified, they look like this: TAXI ( Id int primary key Name varchar ) ORDER ( Id int primary key RedirectedFrom int foreign key on TAXI.Id RedirectedTo int foreign key on TAXI.Id ) So, two foreign keys from table TAXI refer to table ORDER Q: Is tha...

Drop primary key index

Can I drop primary key index without dropping primary key constraint in postgresql? ...

MySQL Error 1452 - Cannot add or update a child row: a foreign key constraint fails

I've looked at other people's questions on this topic but can't seem to find where my error is coming from. Any help would be greatly appreciated. I'm including as much as I can think of that might help find the problem: CREATE TABLE stocks ( id INT AUTO_INCREMENT NOT NULL, user_id INT(11) UNSIGNED NOT NULL, ticker VARCHAR(20) NOT NUL...

Symfony 1.4: Deleting a sfGuardUser

Hi, I'm having some trouble with the following... I have a sfGuardUser table set up normally, and it has a one-to-one relationship with a Profile table, which contains some additional user info. When a user wants to delete themselves from the site, I'd like to retain their info in the Profile table for various purposes BUT delete the ...

Way to check for foreign key references before deleting in MySQL?

I'm working with a content management system, and users are prompted with a confirmation screen before deleting records. Some records are foreign key referenced in other tables, and therefore they cannot be deleted. I would like to display a message beside a given record if it has foreign key references. To know whether I should display...

Hibernate criteria query using Max() projection on key field and group by foreign primary key

I'm having difficulty representing this query (which works on the database directly) as a criteria query in Hibernate (version 3.2.5): SELECT s.* FROM ftp_status s WHERE (s.datetime,s.connectionid) IN (SELECT MAX(f.datetime), f.connectionid FROM...

Separating weakly linked database schemas

I've been tasked with revisiting a database schema we designed and use internally for various ticketing and reporting systems. Currently there exists about 40 tables in one Oracle database schema supporting perhaps six webapps. However, there's one unifying relationship amongst them all: a rooms table describing the room. Room name, pu...

Database Design Composite Keys

I am going to use a contrived example: one headquarter has one-or-many contacts. A contact can only belong to one headquarter. TableName = Headquarter Column 0 = Id : Guid [PK] Column 1 = Name : nvarchar(100) Column 2 = IsAnotherAttribute: bool TableName = ContactInformation Column 0 = Id : Guid ...

Why can't I SELECT a parent field that doesn't have a child?

(rest_branches) is the table of restaurants. (phone_numbers) is another table which contains the restaurants phone_numbers, and it has a field called (branch_id) which references the restaurant id. When I try: SELECT * FROM rest_branches NATURAL JOIN phone_numbers I only get the restaurants which have a phone_number. What should I ...

SQL server deadlock between INSERT and SELECT statement

Hi! I've got a problem with multiple deadlocks on SQL server 2005. This one is between an INSERT and a SELECT statement. There are two tables. Table 1 and Table2. Table2 has Table1's PK (table1_id) as foreign key. Index on table1_id is clustered. The INSERT inserts a single row into table2 at a time. The SELCET joins the 2 tables. (it'...

Creating an insert query (error of foreign key constrant)

I want to move data from one database's table to another database's table. It is giving me a foreign key error. Please tell me how I can insert all those data which is valid except those rows who don't have a foreign key. I am using SQL Server 2005 My query is : SET IDENTITY_INSERT City ON INSERT INTO City ([cityid],[city],[countr...

Easy way to observe user activity - how improve my database structure.

Welcome, I need some advise to improve perfomence my web application. In the begin I had this structure of database: USER -id (Primary Key) -name -password -email .... PROFILE -user Primary Key, Foreign Key (USER) -birthday -region -photoFile ... PAGES -id (Primary Key) -u...

Populating a foreign key table with variable user input

I'm working on a website that will be based on user contributed data, submitted using a regular HTML form. To simplify my question, let's say that there will be two fields in the form: "User Name" and "Country" (this is just an example, not the actual site). There will be two tables in the database : "countries" and "users," with "user...