foreign-keys interview questions
Top foreign-keys frequently asked interview questions
Are disabling and enabling foreign key constraints supported in SQL Server? Or is my only option to drop
and then re-create
the constraints?
Source: (StackOverflow)
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.
Source: (StackOverflow)
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
Source: (StackOverflow)
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;
Source: (StackOverflow)
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.
Source: (StackOverflow)
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.
Source: (StackOverflow)
Is there a nice easy way to drop all tables from a MySQL database, ignoring any foreign key constraints that may be in there?
Source: (StackOverflow)
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:
- A list of tables that have foreign keys into X
- AND which of those tables actually have values in the foreign key
Thanks!
Source: (StackOverflow)
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.
Source: (StackOverflow)
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)?
Source: (StackOverflow)
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
Source: (StackOverflow)
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:
How does build work so that Rails is smart enough to figure out how to save the records in the right order?
How can I do the same thing in a belongs_to
relationship?
Where can I find any documentation on this method?
Thank you
Source: (StackOverflow)
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?
Source: (StackOverflow)
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]
Source: (StackOverflow)
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:
Beside FK helps on relationship constraint, does it have any other usefulness? Does it improve query performance?
Should I create index on all FK columns (liked Products.CategoryId) in all tables?
Source: (StackOverflow)