Foreign keys are so valuable and renowned that they are categorized as level 3 in the database normalization stages. In 3NF, all of the attributes (columns) in a table are directly dependent on the primary key of that table, and there are no transitive dependencies.
Introducing the foreign keys can help maintain the requirements of 3NF by ensuring that the values in a foreign key column only exist in the referenced primary key.
The goal of this tutorial is to show you how to create and use a foreign key in an Oracle database. This tutorial is oriented toward the beginners, and we do not cover on how to implement the foreign keys in database normalizations.
Create a Foreign Key Statement
We can create a foreign key during table creation using the CREATE TABLE statement and the FOREIGN KEY clause.
We can express the syntax as shown in the following:
column1 datatype,
column2 datatype,
...
FOREIGN KEY (column1, column2, ...)
REFERENCES other_table_name (other_column1, other_column2, ...)
);
The table_name refers to the table that you wish to create in the database. We also define the table columns, their corresponding data type, and various constraints.
Finally, we call the FOREIGN KEY clause to specify the column or columns that we wish to make up the foreign key references.
To best illustrate the use of foreign keys, let us take two tables as shown in the following create statements:
(
id number generated by default on null as identity,
name VARCHAR2(50) NOT NULL,
default_port NUMBER,
latest_version VARCHAR2(20) NOT NULL,
type VARCHAR2(20) NOT NULL,
language VARCHAR2(20) NOT NULL,
FOREIGN KEY (id)
REFERENCES size_stats (database_id)
);
Table 2:
database_id number primary key,
size_on_disk number not null,
size_compressed number
);
In the first clause, we create a table to store the database information such as the database name, default port, latest database version, the database type, and the language.
In addition, we include the FOREIGN KEY clause which defines a new foreign key on the id column of the database table. This foreign key references the database_id in the size_stats table. Therefore, it is good to ensure that the size_stats table exists before creating the databases table.
Once we have the sample tables setup, let us insert the sample data in the table for demonstration.
insert into size_stats(database_id, size_on_disk, size_compressed) values (2, 567.67, 344.43);
insert into size_stats(database_id, size_on_disk, size_compressed) values (3, 649.30, 500.13);
insert into size_stats(database_id, size_on_disk, size_compressed) values (4, 45.62, 39.96);
insert into size_stats(database_id, size_on_disk, size_compressed) values (5, 1000.12, 986.43);
Next, add the data to the databases table as shown in the following:
VALUES ('Oracle', 1521, '19c', 'relational', 'SQL');
INSERT INTO databases (name, default_port, latest_version, type, language)
VALUES ('MySQL', 3306, '8.0', 'relational', 'SQL');
INSERT INTO databases (name, default_port, latest_version, type, language)
VALUES ('PostgreSQL', 5432, '13', 'relational', 'SQL');
INSERT INTO databases (name, default_port, latest_version, type, language)
VALUES ('MongoDB', 27017, '4.4', 'non-relational', 'JavaScript');
INSERT INTO databases (name, default_port, latest_version, type, language)
VALUES ('Microsoft SQL Server', 1433, '2017', 'relational', 'T-SQL');
The first insert statements should give us a table that holds the database size stats as shown in the following:
The second table holds the database information as shown in the following:
Once the foreign key is applied, it ensures that the provided data is intact and no invalid data is added.
For example, if we attempt to add a new database with an id that does not exist in the stats table, it should return an error as follows:
VALUES ('SQLite', null, '3.34', 'relational', 'C');
Resulting Error:
Drop a Foreign Constraint
You can remove an existing foreign constraint name using the ALTER TABLE command as shown in the following:
DROP CONSTRAINT foreign_key_name;
Enable/Disable a Foreign Key
We can also disable or enable a foreign key without removing it from the table as shown in the following commands:
DISABLE CONSTRAINT foreign_key_name;
To enable a foreign key, use the following command:
ENABLE CONSTRAINT foreign_key_name;
Conclusion
In this tutorial, you learned how to use the foreign keys in Oracle tables to improve and provide data integrity.
from https://ift.tt/ZxgqLze
0 Comments