If you're an entrepreneur, you must have recognized the value and need for data in your business. The ability to store and edit databases increases the value of the company.
Databases are organized according to a certain convention and allow data to be structured into connections, leading us to relational databases, which have been adopted as a form ofDatengestaltungSince the 1970s. And in today's market, relational databases are favored for their data manipulation capabilities.
Although there are many relational databases available,MySQLhas reached the top spot and is according to the data number two in the worldstatesman, from January 2022.
In SQL Server, constraints are predefined rules and constraints that are enforced on either single or multiple columns. are associated with the values in the column and help maintain the integrity, accuracy, and reliability of the data in the specified columns.
In short, only data that satisfies the constraint rule is successfully inserted into the column. The import process will stop if the data does not meet the criteria.
This post assumes that you have come across relational databases, particularly MySQL, and are keen to expand your knowledge in the field. Finally, I provide some tips for interacting with foreign key constraints.
Primary Key Constraints Summary
A table in SQL contains one or more columns that contain key values that precisely identify each row in the systems. The primary key (PK) column of the table is responsible for enforcing the entity integrity of the table. Primary key constraints guarantee unique data and are often defined in an identity column.
After you specify the primary key constraints for your table, the database engine automatically enforces data uniqueness by creating unique indexes on each of the primary columns. Primary keys offer a great advantage when used in queries because they allow quick access to data.
When primary key constraints are defined for multiple columns, it is called a composite or complex primary key. And in this case, each primary key column can contain duplicate values. However, the combined values of all columns in the primary key must be unique.
A good example is a case where you have a table with "ID", "names" and "bee`. If you set the primary key constraint to the combination "ID"and"names", you can have duplicate instances of both "ID"Die "names` Values. However, each combination must be unique to avoid duplicate rows. So you could have entries with `id=1"and"Name=Walter", And "Bee-22"and `id=1`,`House=Erik's"and"Bee = 27`, but you cannot have other entries with `id=1"and"Name=WalterBecause the combination is not unique.
Here are some important aspects to keep in mind:
- A table contains only one primary key constraint.
- Primary keys cannot exceed 16 columns and a maximum length of 900 characters.
- Indexes created from primary keys can extend the indexes on the table. However, the number of clustered indexes on a table cannot exceed 1 and the number of non-clustered indexes on a table is limited to 999.
- If grouping and ungrouping are not specified for a key constraint, grouping is preserved automatically.
- All columns declared within a primary key constraint must be defined as non-null. If not, all columns bound to the constraint are automatically set to nulls or non-nullables.
- When primary keys are set toCommon Language Runtime (CLR)For a user-defined column type, the type implementation must support binary ordering.
Foreign key constraints overview
A foreign key (FK) denotes a column or combination of several that is used to create and bind a link between two tables and manages the data to be stored in a foreign key table.
A foreign key reference creates a join between two tables. when one or more columns containing the primary key of another table are referenced by one or more columns of another table.
In the foreign key reference scenario, a relationship between two tables is created when one or more primary key columns in one table are referenced by columns in another table.
In practical case you can have a table,Sales.SalesOrderHeader, with a foreign key related to another table,discounts. Face, because there is a logical relationship between salespeople and sales orders.
Here oneSeller IDin the columnSalesOrderHeadermixed with primary key column isaleTable. Theprovider tableis a foreign keySeller IDSpalte iSalesOrderHeader.
This relationship defines a rule: aSeller ID The value cannot be in youSalesOrderHeadertable if not presentsale Bord.
A table can reference up to 253 other columns and tables as foreign keys, also called outbound references. Since 2016, SQL Server has increased the number of tables and columns you can reference in a single table (also known as inbound references) from 253 to 10,000. However, the increase comes with some caveats:
- Foreign key references greater than 253 are only available for DELETE DML operations. MERGE and UPDATE are not supported.
- Tables with foreign key references to themselves have up to 253 foreign key references.
- For columnstore indexes, memory-optimized tables, and partitioned foreign key tables, foreign key references are limited to 253.
What are the advantages of foreign keys?
As mentioned earlier, foreign key constraints play an important role in ensuring the integrity and consistency of relational database data. Here's a summary of why foreign key constraints are important.
- Referential Integrity– Foreign key constraints guarantee that each child table record corresponds to a parent table record, ensuring data consistency across both tables.
- Prevention of orphan files– When you drop a parent table, foreign key constraints ensure that the related child table is also dropped, preventing orphan records that could lead to data inconsistencies.
- Improved performance– Foreign key constraints increase query performance by allowing the database management system to optimize queries based on table relationships.
Foreign Key Constraint Indexes
Foreign key constraints do not automatically create equivalent primary indexes. You can create foreign key constraint indexes manually. is advantageous for the following reasons.
- Foreign key columns are commonly used in join criteria when combining data from related tables in queries by matching the columns associated with the constraint. Indexes help the database find relevant data in a foreign table.
- When you change primary key constraints, they are checked against foreign constraints on related tables.
Indexing is not mandatory. You can still join data from two tables without specifying primary and foreign key constraints. However, adding foreign key constraints will optimize the tables and combine them into a query that meets the criteria for using the keys. When you change the primary key constraints, they are compared to the foreign constraints of the related keys.
Tips for creating foreign key constraints in SQL
You've already spent too much time speculating. Answer Why Let's shift our focus and narrow it down to the tactic of creating foreign key constraints. answer how.
A "foreign key" field in one table refers to the "primary key" of another. The table with the primary key is your parent table. And the table with the foreign key is called the child table. let's dive in
Create a foreign key when creating a table
When you create a table, you can also create a foreign key constraint to maintain referential integrity. That's how it's done:
CREATE TABLEorders(order_id INT PRIMARY KEY, customer_id INT, order_date DATE, FOREIGN KEY(customer_id) REFERENCES customer(customer_id));The code above creates a table named "orders" with a primary integer key "order_id", a second integer "customer_id" and the date "order_date". In this case, the FOREIGN KEY constraint is added to the customer_id column and points to customer_id in your customers table.
Create a foreign key after table creation
Suppose you have already created a table and want to add a foreign key constraint. use `CHANGE TABLE` statement in your code. See the code snippet below.
ALTER TABLE Bestellungen ADD FOREIGN KEY (customer_id) REFERENCES customer (customer_id);In this case, you added a foreign key constraint on the customer_id column in the orders table to refer to the customer_id column in the customer table.
Create a foreign key without searching for existing data
When you add a foreign key constraint to a table, the database automatically looks up existing data to ensure compliance with the constraint. However, if you know the data is consistent and want to add a constraint without a consistency check, do the following.
ALTER TABLE Bestellungen ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES kunder(customer_id)IKKE VALIDERING;TheNOT VALIDATEDThe command tells the database not to search for existing data. This special case is useful in certain situations. For example, if you have large amounts of data and want to complete the validation process.
Create foreign keys via DELETE/UPDATE
When you create foreign key constraints, you can specify what action to take when the referenced row is updated or deleted. In this case, you use scalar referential integrity constraints to dictate the actions that must be taken. They include:
#1.NO ACTION
As with many other databases, the NO ACTION rule is the default behavior when creating a foreign key constraint. This means that no action is taken if the referenced row is deleted or updated.
The database engine issues an error if the foreign key constraint is violated. However, this is not recommended as it can cause referential integrity issues since the foreign key constraint needs to be enforced. Here is an example:
ALTER order TABLE ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id)REFER customer(customer_id)ON DELETE NO ACTION ON UPDATE NO ACTION.#2.SEQUENCE
The "CASCADE" rule is another option for the "ON DELETE" and "ON UPDATE" actions when creating foreign key constraints. If present, it means that whenever a row in the parent tables is updated or deleted, the referenced rows are updated or deleted accordingly. This technique is robust when it preserves referential integrity. Here is an example:
ALTER order TABLE ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id)REFERENCES kunder(customer_id)ON SLET CASCADEON UPDATE CASCADE.You should be careful when applying this rule, because if used improperly, it can lead to undesirable consequences. You want to avoid accidentally deleting too much data or creating rolling reports. Therefore, use this option only when necessary and with caution.
There are some rules for using CASCADE:
- You cannot specify CASCADE when a timestamp column is part of the foreign key or reference key.
- If your table has an ON DELETED trigger, you cannot specify ON DELETED CASCADE.
- You cannot specify ON UPDATE CASCADE if your table has an AGAINST UPDATE rule.
#3.SET ZERO
If you delete or update a corresponding row in the parent table, all values that make up the foreign key are set to zero. This throttling rule requires foreign key columns to be null to run and cannot be specified for tables with INSTEAD OF UPDATE tag rules. Here's an example of how to do it.
ALTER TABLE Bestellungen ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES kunder(customer_id)ON DELETE SET NULLON UPDATE SET NULLIn this case, you have the foreign key column "customer_id" in the "Orders" table set to null when the corresponding row in the "customers" table is deleted or updated.
#4.DEFAULT SETTING
Here you set all the values that make the foreign key the default if the referenced row in the parent table is updated or deleted.
This constraint is enforced when all foreign key columns have default definitions. If a column is null, its default value is set to NULL. Note that this option cannot be specified for tables with INSTEAD OF UPDATE indicators. Here is an example:
ALTER TABLEorders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES kunder(customer_id) ON SET SLET DEFAULT UPDATE SET DEFAULT.In the above case, you have set the "customer_id" in the "orders" table to the default value, which happens when the corresponding row in the "customers" table is deleted or updated.
last words
In this guide, you refreshed on primary key constraints and covered foreign key constraints. You've also come across various techniques for creating foreign key constraints. And although there are many ways to create foreign key constraints, this post has introduced the methods.
And I hope you get new techniques. You are not limited to combining them. For example, the constraint methods CASCADE, SET NULL, SET DEFAULT, and NO ACTION can be combined for tables with reference relationships.
If your table sees NO ACTIVITY, it is subject to different throttling rules. In other cases, a DELETE action can trigger a combination of these rules and the NO ACTION rule is executed last.
Then checkSQL error.