Hi there
I was just wondering why I should explicitly create a foreign key,
especially if i'm not concerned with Cascading/Deleting when a row is
changed.
For example, let's say I have a USERS table, with the column 'USERID' as a
primary key. Other columns would be 'NAME' and 'EMAIL'.
In my users table I have two rows: ["1","Joshua","t e**@example.com "] and
["2","Mark","ma* *@example.com"].
Now, let's say I have a table that records a user's favorite film
(FAVEFILMS). Columns would be "FAVEID" (auto-increment key), "USERID"
(indexed because we will be looking up by userID) and "FILM".
In this favefilms table, let's put two rows: ["1","2","Ja ws"] and
["2","1","Romanc ing the Stone"].
Obviously there is a relationship between the USERID in the users table and
the USERID in the favefilms table, but what is the purpose of explicitly
defining the USERID in the favefilms table as a foreign key?
If I'm writing a select statement to find Joshua's favorite film, I would
just say "SELECT FILM FROM FAVEFILMS WHERE USERID='1'".
If I've indexed the UserID column as a regular index (as should be done
with any column that will be used in a WHERE clause), does defining it as a
foreign key add anything useful? or does NOT defining it as a foreign key
create any harm?
Just wondering... I mean, hey I have no problem specifying foreign keys, I
was just wondering what the purpose was if I was not interested in
CASCADING or DELETING when changes happen to a table....
Much thanks. I've been using MySQL in web applications for a long time, but
am about to embark on a really big, very important project, so i'm going
back to MySQL 101 to brush up on things I didn't pay much attention to
earlier....
- j
1  1501 
The reason is very simple - it avoids that you can delete a row in the
parents table where there are still records in the child table, or that you
can add records to the child table that have no corresponding record in the
parents table.
Of course you could say that with properly written scripts it couldn't
happen anyway, but it gives you an extra plus of security to ensure that you
keep the integrity, no matter what happens ;-).
Markus
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Phil Powell |
last post by:
create table if not exists nnet_produkt_varegruppe (
nnet_produkt_varegruppe_id int not null auto_increment,
primary key(nnet_produkt_varegruppe_id),
nnet_produkt_varegruppe_navn varchar(255) not null
);
create table if not exists nnet_produkt_farge (
nnet_produkt_farge_id int not null auto_increment,
primary key(nnet_produkt_farge_id),
|
by: Morten Gulbrandsen |
last post by:
USE company;
DROP TABLE IF EXISTS EMPLOYEE;
CREATE TABLE EMPLOYEE
(
# PK
SSN CHAR(9) NOT NULL,
# FK
SUPERSSN CHAR(9),
DNO INT NOT NULL DEFAULT 1,
CONSTRAINT EMPPK
|
by: teedilo |
last post by:
Our MS SQL (SQL Server 2000) DBA has database privileges locked down
pretty tightly. We end users/developers do not have administrator
privileges for most databases. That arrangement has worked out OK for
the most part. However, it's a bit aggravating that we can't even
create our own database diagrams. When we attempt to do so (in
Enterprise Manager), we get a dialog that says "You do not have
sufficient privilege to create a new...
|
by: bobby_b_ |
last post by:
I have a table where fields 1 and 2 make up the primary key.
Because of this, I have a unique composite index on fields 1 and 2 (as
required by DB2). Now my question is: Fields 1 and 2 are also
separate foreign keys to two other tables. I have read that it is
always a good idea to create indexes on foreign keys. Should I create
single indexes on each of these fields? Or is that not necessary since
they are already part of a composite...
|
by: max |
last post by:
Hello,
I am a newbye, and I'm trying to write a simple application.
I have five tables with three columns; all tables are identical; I need to
change some data in the first table and let VB updates the same data in all
other four tables in the right places.
I know it would be possible by using the ForeignKeyConstraint object. I have
created the tables using the DataSet Visual Tool and I know it doesn't
create any ForeignKeyConstraint obj....
| | |
by: raylopez99 |
last post by:
Keep in mind this is my first compiled SQL program Stored Procedure
(SP), copied from a book by Frasier Visual C++.NET in Visual Studio
2005 (Chap12). So far, so theory, except for one bug (feature?)
below. At some point I'm sure I'll be able to laugh about this, akin
to forgeting a semi-colon in C/C++, but right now it's frustrating
(time to sleep on it for a while).
Problem--
For some reason I get the error when trying to save files...
|
by: Fischi |
last post by:
Hi, I am getting the error message
Script line: 84 Can't create table '.\dwh\f_umsatz.frm' (errno: 150)
during execution of the following script on a windows system and mysql 5.0:
CREATE TABLE d_kunden(id INT NOT NULL AUTO_INCREMENT,
|
by: Alvin SIU |
last post by:
Hi all,
I have a table in Db2 v8 like this:
Team Name Role
------ -------- ---------------------
A Superman Leader
A Batman Member
A WonderWoman Member
B Alvin Leader
|
by: jarea |
last post by:
I have read quite a bit about this error but I have yet to find the solution to my problem.
I am trying to execute the following mysql statement:
alter table line_items
add constraint fk_line_item_products
foreign key (product_id) references products(id)
I have also tried the following statement with the same result:
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
