foreign-keys

How to add a foreign key, through the interface, to ASPNET Membership provider?

I'm using VWD 2008 Express Edition with MVC and I am trying to add a foreign key from my database to the ASPNET Membership database's Users table. Is this possible using the user interface or do I have to scrounge up some SQL every time I want to do this? Edit: After thinking about this for a minute, I realized that maybe having an FK t...

Adding foreign key constraint to 'partial' primary key in PostgreSQL

I am trying to add a foreign key constraint to a table in my PostgreSQL 8.4 database, but it is failing as the target field, though part of a multi-column primary key, is not in itself unique. The database has the following structure: Table 1 (names of primary IDs): PrimaryType, Name [Primary key = "PrimaryType"] Table 2 (names of c...

When would you ever need to change a primary key's value?

Hello, I've been reading up on foreign keys and such for postgres and I noticed that it allows a cascading update for foreign keys. Well, my question is, when would you need to update the primary key of a row? Apparently this guy needs to http://www.oreillynet.com/onlamp/blog/2004/10/hey_sql_fans_check_out_foreign.html but I'm not qu...

Why doesn't Oracle have foreign keys in Oracle Apps/ E-Business Suite?

Why doesn't Oracle have foreign keys in Oracle Apps/ E-Business Suite? I observed that Oracle doesn't have/use referential constraints on the Oracle Apps 11i Schema. All the logic is contained in PL/SQL packages. This seems odd coming from a RDBMS major. ...

Is it necessary to have a Primary key which is sequence generated even though I may not use it for DB operations

Hi I had parent table which has ID and some other columns and a child table which have many values based on this ID(foreign Key). I wanted to create the table with a Primary Key which is sequence and this Parent table ID as Foreign Key but later I found I had one more Foreign Key EMPID which on combination provides uniqueness. Even for r...

How to convert clustered primary key to non-clustered without dropping referring foreign keys in SQL Server 2005

I've made mistake creating clustered primary key on GUID column. There are many tables that reference that table with defined foreign keys. Table size is not significant. I would like to convert it from clustered to non-clustered without manually dropping and recreating any foreign keys or even primary key constraint. Is it possible to...

How can I create a foreign key on a column, every record of which may refer to a column in one of several tables?

I'm in the process of creating a social network. It has several entities like news, photo, which can have comments. Since all comments have the same columns and behave the same way, and the only difference is their type — news, or photo, or something else to be added in the future — I decided to create one table for all comments with a c...

NHibernate - How to find if a referenced object can be deleted?

I have an object called "Customer" which will be used in the other tables as foreign keys. The problem is that I want to know if a "Customer" can be deleted (ie, it is not being referenced in any other tables). Is this possible with Nhibernate? Thanks ...

Is it good practice to have several foreign keys in one table?

Hello, Consider the following table structure: titles(titleID*, titleName) platforms(platformID*, platformName) products(platformID*, titleID*, releaseDate) publishers(publisherID*, publisherName) developers(developerID*, developerName) products_publishers(platformID*, titleID*,publisherID*) products_developers(platformID*,...

Possible to condense primary key/serial?

Hello, I have a database where everything is linked with foreign keys, so the Postgres knows exactly how the database is layed out.. Well, Lets say I have Table1 and Table2. Table1 has 3 fields. RID, table2_rid,data So table1.table2_rid references table2.RID and this is expressed with a foreign key. In both the RID field is the prima...

Force drop mysql bypassing foreign key constraint

I'm trying to delete all tables from a database except one, and I end up having the following error: Cannot delete or update a parent row: a foreign key constraint fails Of course I could trial and error to see what those key constraints are and eventually delete all tables but I'd like to know if there is a fast way to force drop al...

Foreign tables in SQL Server updated with Primary Key

The title might be a little misleading because I didn't really know what to call it. Here is my dbml and I am using the repository pattern to communicate between my application layer and my SQL layer. As you can see, I have four tables. Three of them have a foreign key to ContactId. I used this method because I need to store an "array", ...

How do I make Django-Piston to include related child objects in the serialized output?

Hi guys! I am pulling my hair out here because this isn't working for me and seems like it should be. I am using Django-Piston to develop an API and have 2 models, Building and Building Area. BuildingArea has a ForeignKey to Building as there are multiple areas in a building. The 'related_name' property for the FK is 'areas' so I can a...

Optimizing delete for a table referenced by lots of foreign keys

I have a table Document that is referenced by a ton of other tables via foreign keys. I am trying to delete a Document record, and according to my execution plan, SQL Server is doing a clustered index scan on every one of the referencing tables. This is very painful. I thought having a FK automatically made an index on the FK fields? ...

Script to add an index on each foreign key?

Seeing as a foreign key does not automatically create an index in SQL Server, I want to create an explicit index on every FK field in my database. And I have over 100 tables in the schema... So, does anyone have a ready packaged script that I could use to detect all FKs and create an index on each? ...

Is there a severe performance hit for using Foreign Keys in SQL Server?

I'm trying my best to persuade my boss into letting us use foreign keys in our databases - so far without luck. He claims it costs a significant amount of performance, and says we'll just have jobs to cleanup the invalid references now and then. Obviously this doesn't work in practice, and the database is flooded with invalid reference...

MySQL foreign key to the same table,failed

mysql> ALTER TABLE category ADD CONSTRAINT category_parent_category_id FOREIGN KEY (parent) REFERENCES category(id); ERROR 1005 (HY000): Can't create table 'sfnews.#sql-244_1' (errno: 150) DDL as follows: Create Table: CREATE TABLE `category` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `parent` bigin...

Is the index on `parent` necessary here?

Create Table: CREATE TABLE `category` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL, `parent` bigint(20) unsigned DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`), KEY `parent_idx` (`parent`), CONSTRAINT `category_parent_category_id` FOREIGN KEY (`parent`) REFERENCES `category`...

MySQL : how to add foreign key

Hello, I have the following code in mysql. create table employee( e_id int(10) not null auto_increment, user_id int(10), usertype_id default 1, name varchar(50), primary key (e_id) ); create table relation( r_id int(10) not null auto_increment, user_id int(10) not null, usertype_id int(10) not null, ...

Why is it still possible to insert a foreign key that doesn't exist?

mysql> create table products(id integer unsigned auto_increment primary key); Query OK, 0 rows affected (0.05 sec) mysql> CREATE TABLE orders ( -> id integer PRIMARY KEY auto_increment, -> product_id integer REFERENCES products (id), -> quantity integer, -> INDEX product_id_idx (product_id) -> ); Que...