SQL FOREIGN KEY Constraint
SQL FOREIGN KEY Constraint
In SQL, the FOREIGN KEY constraint is used to establish a relationship between two tables. It is a way to enforce referential integrity in a relational database. The FOREIGN KEY constraint ensures that the values in a column or a set of columns of one table match the values in a column or set of columns in another table.
Here is an example of how to define a foreign key in SQL:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
total DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
In this example, we have defined a table called “orders” with four columns: “order_id”, “customer_id”, “order_date”, and “total”. The customer_id column is a foreign key that references the customer_id column in the customers table. This means that the values in the customer_id column of the “orders” table must match the values in the customer_id column of the “customers” table.
Here is an example of how to insert data into the “orders” table with a foreign key:
INSERT INTO orders (order_id, customer_id, order_date, total)
VALUES (1, 12345, '2022-03-27', 100.00);
In this example, we are inserting a new row into the “orders” table with values for the “order_id”, “customer_id”, “order_date”, and “total” columns. Since the customer_id column is a foreign key, we must provide a valid customer ID from the customers table in the INSERT statement. If we try to insert a row with a non-existent customer ID, the database will return an error.
In summary, the FOREIGN KEY constraint is used to establish a relationship between two tables in SQL. It is essential for maintaining data integrity and ensuring the accuracy and consistency of data in a database.