EzDevInfo.com

How can foreign key constraints be temporarily disabled using T-SQL?

Are disabling and enabling foreign key constraints supported in SQL Server? Or is my only option to drop and then re-create the constraints?

How do I add a foreign key to an existing sqlite (3.6.21) table?

I have the following table:

CREATE TABLE child( 
  id INTEGER PRIMARY KEY, 
  parent_id INTEGER, 
  description TEXT);

How do I add a foreign key constraint on parent_id? Assume foreign keys are enabled.

Most examples assume you're creating the table - I'd like to add the constraint to an existing one.

How to remove constraints from my MySQL table?

I want to remove constraints from my table. My query is:

ALTER TABLE `tbl_magazine_issue` 
DROP CONSTRAINT `FK_tbl_magazine_issue_mst_users`

But I got an error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'constraint FK_tbl_magazine_issue_mst_users' at line 1

truncate foreign key constrained table

Why doesn't a TRUNCATE on mygroup work? Even though I have ON DELETE CASCADE SET I get:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) REFERENCES mytest.mygroup (ID))

drop database mytest;
create database mytest;
use mytest;

CREATE TABLE mygroup (
   ID    INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE instance (
   ID           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   GroupID      INT NOT NULL,
   DateTime     DATETIME DEFAULT NULL,

   FOREIGN KEY  (GroupID) REFERENCES mygroup(ID) ON DELETE CASCADE,
   UNIQUE(GroupID)
) ENGINE=InnoDB;

When to use "ON UPDATE CASCADE"

I use "ON DELETE CASCADE" regularly but I never use "ON UPDATE CASCADE" as I am not so sure in what situation it will be useful.

For the sake of discussion let see some code.

CREATE TABLE parent (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id)
);

CREATE TABLE child (
    id INT NOT NULL AUTO_INCREMENT, parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
);

For "ON DELETE CASCADE", if a parent with an id is deleted, a record in child with parent_id = parent.id will be automatically deleted. This should be no problem.

(1) This means that "ON UPDATE CASCADE" will do the same thing when id of the parent is updated?

(2) If (1) is true, it means that there is no need to use "ON UPDATE CASCADE" if parent.id is not updatable (or will never be updated) like when it is AUTO_INCREMENT or always set to be TIMESTAMP. Is that right?

(3) If (2) is not true, in what other kind of situation should we use "ON UPDATE CASCADE"?

(4) What if I (for some reason) update the child.parent_id to be something not existing, will it then be automatically deleted?

Well, I know, some of the question above can be test programmically to understand but I want also know if any of this is database vendor dependent or not.

Please shed some light.

Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)?

I know that I can either

• Use a DELETE without a where clause and then RESEED the identity (or)
• Remove the FK, truncate the table, and recreate the FK.

I thought that as long as I truncated the child table before the parent, I'd be okay without doing either of the options above, but I'm getting this error:

Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.

MySQL DROP all tables, ignoring foreign keys

Is there a nice easy way to drop all tables from a MySQL database, ignoring any foreign key constraints that may be in there?

MySQL: How to I find all tables that have foreign keys that reference particular table.column AND have values for those foreign keys?

I have a table whose primary key is referenced in several other tables as a foreign key. For example:

  CREATE TABLE `X` (
    `X_id` int NOT NULL auto_increment,
    `name` varchar(255) NOT NULL,
    PRIMARY KEY  (`X_id`)
  )
  CREATE TABLE `Y` (
    `Y_id` int(11) NOT NULL auto_increment,
    `name` varchar(255) NOT NULL,
    `X_id` int DEFAULT NULL,
    PRIMARY KEY  (`Y_id`),
    CONSTRAINT `Y_X` FOREIGN KEY (`X_id`) REFERENCES `X` (`X_id`)
  )
  CREATE TABLE `Z` (
    `Z_id` int(11) NOT NULL auto_increment,
    `name` varchar(255) NOT NULL,
    `X_id` int DEFAULT NULL,
    PRIMARY KEY  (`Z_id`),
    CONSTRAINT `Z_X` FOREIGN KEY (`X_id`) REFERENCES `X` (`X_id`)
  )

Now, I don't know how many tables there are in the database that contain foreign keys into X like tables Y and Z. Is there a SQL query that I can use to return:

1. A list of tables that have foreign keys into X
2. AND which of those tables actually have values in the foreign key

Thanks!

MySQL : show constraints on tables command

I have tables that I've tried setting PK FK relationships on but I want to verify this. How can I show the PK/FK restraints? I saw this manual page, but it does not show examples and my google search was fruitless also. My database is credentialing1 and my constrained tables are practices and cred_insurances.

How to find foreign key dependencies in SQL Server?

How can I find all of the foreign key dependencies on a particular column?

What are the different alternatives (graphically in SSMS, queries/views in SQL Server, 3rd party database tools, code in .NET)?

Mysql error 1452 - Cannot add or update a child row: a foreign key constraint fails

I'm having a bit of a strange problem, I'm trying to add a foreign key to one table that references another, but it is failing for some reason. With my limited knowledge of MySQL, the only thing that could possibly be suspect is that there is a foreign key on a different table referencing the one I am trying to reference.

Here is a picture of my table relationships, generated via phpMyAdmin: Relationships

I've done a SHOW CREATE TABLE query on both tables, sourcecodes_tags is the table with the foreign key, sourcecodes is the referenced table.

CREATE TABLE `sourcecodes` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(11) unsigned NOT NULL,
 `language_id` int(11) unsigned NOT NULL,
 `category_id` int(11) unsigned NOT NULL,
 `title` varchar(40) CHARACTER SET utf8 NOT NULL,
 `description` text CHARACTER SET utf8 NOT NULL,
 `views` int(11) unsigned NOT NULL,
 `downloads` int(11) unsigned NOT NULL,
 `time_posted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `user_id` (`user_id`),
 KEY `language_id` (`language_id`),
 KEY `category_id` (`category_id`),
 CONSTRAINT `sourcecodes_ibfk_3` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `sourcecodes_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `sourcecodes_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

CREATE TABLE `sourcecodes_tags` (
 `sourcecode_id` int(11) unsigned NOT NULL,
 `tag_id` int(11) unsigned NOT NULL,
 KEY `sourcecode_id` (`sourcecode_id`),
 KEY `tag_id` (`tag_id`),
 CONSTRAINT `sourcecodes_tags_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1

It would be great if anyone could tell me what is going on here, I've had no formal training or anything with MySQL :)

Thanks.

Edit: This is the code that generates the error:

ALTER TABLE sourcecodes_tags ADD FOREIGN KEY (sourcecode_id) REFERENCES sourcecodes (id) ON DELETE CASCADE ON UPDATE CASCADE

Ruby on Rails. How do I use the Active Record .build method in a :belongs to relationship?

I have been unable to find any documentation on the .build method in Rails (i am currently using 2.0.2).

Through experimentation it seems you can use the build method to add a record into a has_many relationship before either record has been saved.

For example:

class Dog < ActiveRecord::Base
  has_many :tags
  belongs_to :person
end

class Person < ActiveRecord::Base
  has_many :dogs
end

# rails c
d = Dog.new
d.tags.build(:number => "123456")
d.save # => true

This will save both the dog and tag with the foreign keys properly. This does not seem to work in a belongs_to relationship.

d = Dog.new
d.person.build # => nil object on nil.build

I have also tried

d = Dog.new
d.person = Person.new
d.save # => true

The foreign key in Dog is not set in this case due to the fact that at the time it is saved, the new person does not have an id because it has not been saved yet.

My questions are:

1. How does build work so that Rails is smart enough to figure out how to save the records in the right order?

2. How can I do the same thing in a belongs_to relationship?

3. Where can I find any documentation on this method?

Thank you

How can I find which tables reference a given table in Oracle SQL Developer?

In Oracle SQL Developer, if I'm viewing the information on a table, I can view the constraints, which let me see the foreign keys (and thus which tables are referenced by this table), and I can view the dependencies to see what packages and such reference the table. But I'm not sure how to find which tables reference the table.

For example, say I'm looking at the emp table. There is another table emp_dept which captures which employees work in which departments, which references the emp table through emp_id, the primary key of the emp table. Is there a way (through some UI element in the program, not through SQL) to find that the emp_dept table references the emp table, without me having to know that the emp_dept table exists?

SQL DROP TABLE foreign key constraint

If I want to delete all the tables in my database like this, will it take care of the foreign key constraint? If not, how do I take care of that first?

GO
IF OBJECT_ID('dbo.[Course]','U') IS NOT NULL
    DROP TABLE dbo.[Course]
GO
IF OBJECT_ID('dbo.[Student]','U') IS NOT NULL
    DROP TABLE dbo.[Student]

Does Foreign Key improve query performance?

Suppose I have 2 tables, Products and ProductCategories. Both tables have relationship on CategoryId. And this is the query.

select p.ProductId, p.Name, c.CategoryId, c.Name AS Category
from Products p inner join ProductCategories c on p.CategoryId = c.CategoryId
where c.CategoryId = 1;

When I create execution plan, table ProductCategories performs cluster index seek, which is as expectation. But for table Products, it performs cluster index scan, which make me doubt. Why FK does not help improve query performance?

So I have to create index on Products.CategoryId. When I create execution plan again, both tables perform index seek. And estimated subtree cost is reduced a lot.

My questions are:

1. Beside FK helps on relationship constraint, does it have any other usefulness? Does it improve query performance?

2. Should I create index on all FK columns (liked Products.CategoryId) in all tables?