When I did my first interview, I faced this confusing question.
join we can join the tables, but what is the main purpose of
FOREIGN KEY in mysql?
Please suggest me the best answer.
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
This fundamental concept endures despite users' and programmers' best efforts to produce systems which render the concept null and void - by that I mean that they frequently try and subvert these (reasonable) requirements of a decent database system. See here for further details on that matter.
As explained in the Wiki, RI is
a property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute (column) in a different (or the same) relation (table).
To give a simple example, that means that if you have a purchase order system with an order_header table with fields as follows:
CREATE TABLE order_header ( order_id INTEGER, client_id INTEGER, order_date DATE, <...other fields depending on your individual requirements...> );
You might then have a
client table like this
CREATE TABLE client ( client_id INTEGER, client_name VARCHAR(50), client_address VARCHAR(50), <.... other fields depending on your requirements...> );
You would then add a
CONSTRAINT to your
order_header table with something like this:
ALTER TABLE order_header ADD CONSTRAINT fk_header_client FOREIGN KEY (client_id) REFERENCES client (client_id); -- the exact syntax may differ per RDBMS.
This is telling the RDBMS that on the
client_id column of the
order_header table, there is now a constraint such that any
order_header must already exist in the
FOREIGN KEY columns(s) on the parent table need to have a
UNIQUE constraint (or be the
Otherwise, it wouldn't make much sense - if there were 2 clients with the same
client_id, how would the system be able to tell them apart - what address do you send the order to?
Note also that with many systems, you can create the
CONSTRAINT in the
CREATE TABLE statement.
Now, your system will be unable to create a purchase order for a client which does not exist. This doesn't mean that the wrong client can't be entered into the system, but it does help to ensure that the system is internally consistent.
Many optimizers can also take advantage of some optimizations when there are
FOREIGN KEY constraints on the joined tables and produce better (more efficient) and simpler query plans.
Finally, a further advantage of using
FOREIGN KEYs is that some systems also can (depending on your RDBMS) automatically create
INDEXes on the
FOREIGN KEY columns.
A foreign key means "ensure the values in this column exist in another column"
create table sales_orders ( order_id int primary key, customer_id int, ... );
customer_id does not have a foreign key (to table
customers), a user could enter a value that does not point to a real customer. But with a foreign key, the value in
customer_id would have to point to a real customer.
A foreign key must point to a unique key or a primary key, and can point to another table or its own table.
A column with a foreign key can be null.
A foreign key can also span more than one column. It has to point to the same number of columns.
Also, you can join tables that do not have foreign keys between them!
The purpose of the foreign key is to ensure referential integrity of the data.
In other words, only values that are supposed to appear in the database are permitted.
check this page please.
Of course we can join two tables, but JOIN will not ensure referential integrity.
A foreign key ensures that a row in a primary table is not deleted if it's referenced by other tables and therefore it guarantees data integrity. See https://technet.microsoft.com/en-us/library/ms175464(v=sql.105).aspx