Having given this some thought, I can't find any realistic scenario that would justify this. However, an unrealistic scenario is:

A table in the database contains information about vehicles owned by a company in the UK. The primary key of this table is REGISTRATION_NUMBER, which in the UK must be displayed on plates at both front and rear of the vehicle. It is commonly used to identify a vehicle, e.g. to buy an electronic parking ticket.

lLess realistically, suppose the table also contains a VIN (vehicle identification number) . The VIN is international and serves a similar role to the REGISTRATION_NUMBER, but there are some differences. Assuming there is some need to identify vehicles by VIN as well as REGISTRATION_NUMBER, it would be sensible to make VIN UNIQUE.

Now, the question asked for a scenario where there is another table with a primary key of VIN. This extra table could be justifiebeause of the differences in how VIN and REGISTRATION_NUMBER work.

In the UK system, lets suppose you own a Ford Focus wih REGISTRATION_NUMBER JMS 1. You then replace the Ford with a Range Rover and, becuase your name is John Michael Smith, you want to keep the REGISTRATION_NUMBER. That's allowed (they're called vanity plates). You can't do this with VIN.

So, in theory at least, the VIN table contains attributes MAKE and MODEL, but all the other attributes (such as COLOUR and ENGINE_SIZE) are in the REGISTRATION_NUMBER table.

Of course, I don't imagine that any corporte database does vanity plates,but ignoring that, what has the VIN table contributed to the functionality o the system? I think, actually, nothing. Just put MAKE and MODEL in the REGISTRATION_NUMBER table.

It seems you’re dealing with foreign key (FK) and primary key (PK) relationships in your database. Let’s break down the potential issues you’ve mentioned:

1. FK Not Pointing to PK on Reference Table: Ensure that your FK field is correctly referencing the PK field in the reference table. The FK should match the data type and value of the PK.
2. Incomplete FK Reference: Make sure your FK references the complete PK. If the PK is a composite key (made up of multiple columns), your FK should include all those columns.
3. FK Not Referencing a Valid Table: Verify that the table your FK references actually exists in your database. If it doesn’t, you’ll encounter issues.
4. Mismatched Data Types: Check if the data types of your FK and PK match. For example, if the PK is a number, the FK should also be a number.

Remember to review your table and field definitions carefully to identify and rectify any issues. If you need further assistance, feel free to provide more details! 😊

Hi,

While referencing the column in tyre_quality table, you did not mention the column name from the main table that is being referenced. Please update the line to

foreign key(id) references vehicle_type(id) 

This should solve the issue.

create table vehicle_type 
(id integer, 
 vehicletype varchar(50), 
 primary key(id,vehicletype) 
 ); 
  
 create table tyre_quality 
 (id integer, 
  tyretype varchar(50), 
  primary key(tyretype), 
  foreign key(id) references vehicle_type(id) 
  ); 

See, the snapshot below for result comparison-

If you get an error even after this change, please drop me a note.

Truncate table with foreign key constraints

SET FOREIGN_KEY_CHECKS = 0;  
TRUNCATE table $table_name;  
SET FOREIGN_KEY_CHECKS = 1; 

The same applies for deleting tables if there are constraints on it.

OR

You cannot TRUNCATE a table that has FK constraints applied on it (TRUNCATE is not the same as DELETE).

To work around this, use either of these solutions. Both present risks of damaging the data integrity.

Option 1:

1. Remove constraints
2. Perform TRUNCATE
3. Delete manually the rows that now have references to nowhere
4. Create constraints

Option 2: suggested by user447951 in their answer

SET FOREIGN_KEY_CHECKS = 0;  
TRUNCATE table $table_name;  
SET FOREIGN_KEY_CHECKS = 1; 

Foreign keys tell the database how to join two or more tables together in the same query. In Atlassian Analytics, foreign keys allow us to join tables together in visual mode queries. For most data source types, Atlassian Analytics can import any foreign keys you've set up already.

To add foreign key in phpMyAdmin use below steps to do;

The key must be indexed to apply foreign key constraint.

1. Open table structure.
2. See the last column action where multiples action options are there. Click on Index, this will make the column indexed.
3. Open relation view and add foreign key constraint.

You will be able to assign <Column_Name> as foreign now.

If you want to add a foreign key to existing column you can do so by SQL statements.

User below SQL Statements:

ALTER TABLE table_name 
    ADD CONSTRAINT fk_foreign_key_name 
    FOREIGN KEY (foreign_key_name) 
    REFERENCES target_table(target_key_name); 

Cheers!

Don’t forget to take account of the cost of not using them. I would underline Brian Rue's point that, apart from being an essential plank of relational integrity, the value of FK constraints in flushing out some kinds of bugs earlier rather than later (or worse, when their absence has allowed damage to production data) is hard to overstate.

Moreover, these aren't the only constraints you will need! You will need NOT NULL, and UNIQUE constraints (sometimes composite, according to the business rules). If MySQL supported them, you'd want CHECK constraints as well.

The advice: "we should never use foreign keys in Web applications" is simply wrong.

Actually you have to design your database in such a way that it can be possible. e.g. is shown as below:

Here, consider 3 tables

1. Table 1 Business system : contains Id field which act as a Primary Key for this table. It contains some different columns like Name, other columns can be of your choice.
2. Table 2 Business Processes: contain Id which is a primary key. It also contains other fields like Name etc.
3. Now Table 3 Business Process details : it has lets say 4 column: one a primary key column of the table, other columns which are foreign key of the tables

Some thing like the above diagram you have to design the database.

I’ll try to answer your question and, at the same time, clarify the answer you linked.

MySQL has different storage engines. Each storage engine implements data writes, reads, indexing, caching, and possibly unique features. Each table is built using one storage engine. The default engine is InnoDB, so if you know nothing about engines, you always use InnoDB (which is usually the best choice).

MySQL knows nothing about foreign keys. It just passes the SQL foreign key syntax to InnoDB, that supports them. So the implementation is completely contained in InnoDB. Both the child and the parent table must be InnoDB.

With other storage engines (MyISAM, MEMORY…) you can use foreign keys syntax, but it is simply ignored, and no foreign key will be created. In the past other engines supported foreign keys, but they are no more maintained and you cannot use them with a modern version of MySQL.

The checks happen when you insert/update a row in a child table, or update/delete a row in a parent table. For changees on the parent table, InnoDB will perform the requested action: restrict (return error), cascade, set null, or simply no action. For changes in the child table, InnoDB will check if there will still be a relation between a child row and a parent row: if you are trying to create an orphan child row, your query will fail with an error. The same happens if you try to drop a parent table, because you are trying to break the parent/child relation.

When InnoDB locks a row in a child table (to guarantee data integrity, for example stopping other users to modify that row until your transaction ends) the corresponding row in the parent table is locked too.

Specifically for MySQL, if you’ve chosen a storage engine that doesn’t support them, (MyISAM, Memory, Archive,CSV,Federated)
Those don’t support transactions either.

Foreign keys also make reloading a database from a logical backup complicated. If you make use of replication; (Statement-base Replication), foreign keys can cause a slave to fail if the tables aren’t loaded order of dependacy or foreign key checks aren’t temporarly disabled. (BTW: the “disable foreign key checks” bit at the top of your script; that doesn’t get replicated.. you have to manually do it on the slaves/replicas before loading your database. Learned that the hard way.

It’s not free. It does add overhead for inserts and updates. If you’re asking this question; your not at the level yet where you’ll have those kinds of problems. Facebook has those kinds of problems. Normals, (myself included) don’t.

Apart from that; It’s a best practice to use them. No project with a DBA isn’t going to use them.
It keeps the data clean. This saves so much time. Chasing down a bug related to orphaned records can be a real PITA. There are lots of tools that can read these relationships and build ERDs which make your database self - documenting.

Your phrasing suggests that when you created the table you did not create the constraint at that time. If that is correct, then you need to consider what may occur as a result of adding it now. Any rows that do not satisfy the constraint will be problematic, and you need to anticipate this before adding the constraint.

Let us suppose that the table of interest, Employees, say, has a column EmailAddress, which, you decide in retrospect, ought to be unique. This could be problematic in a couple of ways. Two of my friends, for example, Malcolm and Chris, live together and in fact even own their house together, but they are unmarried and Chris has chosen to keep her maiden name (don’t you love how sexism is built in to our language?). They have chosen to share a single email address. As a result, there are two rows in Employees, one for each of them, and these rows have a single EmailAddress. So adding a unique constraint on Employees.EmailAddress would be problematic. One solution would be to create a new table EmailAddresses, which you could populate with a command such as:

SELECT DISTINCT EmailAddress
FROM Employees
INTO EmailAddresses;

I leave it to you to decide whether or not to add to the new table a new column, of AutoIncrement type, and declare it the table’s primary key, or alternatively, to make the Email column the primary key. (That would in my opinion be simpler, but if there are thousands of Employees then an AutoIncrement column would be better as the PK for this new table).

Back to the original issue: what to do with rows that violate the proposed constraint? You can’t delete the duplicate rows or else you’d lose the rest of the data about the second Employee (and who knows, there might even be three who share a single email). In short, you need a plan before imposing a post-facto constraint.

Let’s suppose you have such a plan for dealing with the consequences of the proposed constraint, and have executed it. Now your Employees table is ready for the addition of the new constraint.

ALTER TABLE Employees ADD CONSTRAINT email_unique UNIQUE (EmailAddress);

I have purposely posed a scenario in which adding a constraint post-facto could be problematic. I did this on purpose, to make you think about how to handle existing rows that would violate the proposed constraint.

In this case, my choice would be to create the new table EmailAddresses and then alter the Employees table to add a foreign key constraint instead of a unique constraint.

ALTER TABLE Employees
ADD CONSTRAINT employees_email_fk FOREIGN KEY (EmailAddress)
REFERENCES EmailAddresses (EmailAddress);

It is quite understandable that the original designer of the table (you?) did not anticipate the problem of a shared email address. Also, I wanted to provide an example of how to handle existing rows that violate the proposed constraint.

I hope this helps.

A unique constraint is an index. A primary key is also an index.

A well-written index will cause a slight slow down and major speed up select queries — that is the trade-off.

Take note of “well-written.” Adding an index to a table does not guarantee faster reads, nor does it guarantee slower writes because create index does not mean your index will fire when you intend it to.

In general, the write-speeds of adding an index aren’t slow enough to be a major concern. If you are running into issues with write speeds on a database, you can’t know for sure unless you A/B the time of the index. In reality, if you are getting slow writes, odds are you have problems in the query, settings, other code, etc.

Indexes are often blamed for many things that aren’t relevant to indexes. Indexing a database is, in my opinion, one of the most difficult things involved in building a database. When people see how adding an index causes this or that issue, they immediately blame the index, but it’s often much more complicated than this.

The purpose of database constraints is to try to prevent, as much as possible, that incorrect data is stored. Every constraint focuses on specific situations that can never be true and prevents that.

So if for example you try to enter an order in the order processing system using a customer number that does not exist in the database yet, or for a product code that doesn't exist, we know that this can impossibly be correct. We don't know WHAT is wrong. Perhaps you typed the customer number wrong. Perhaps you expected your coworker to have entered the new customer data already. Perhaps you are trying to defraud the company. Regardless of why, as long as the customer does not exist in the database, we cannot record their orders.

Databases are not magic. They need to be told which constraints to enforce. That's part of database design and data modeling.

So to answer your question, why can't you add or change that row … because you're trying to store information that during design has been identified as impossible to be correct, and then the database was tasked with preventing it from being stored.

I believe it's so that a new INSERT/UPDATE can check for duplicate key violations by accessing only one partition.

This is not necessary for non-unique indexes. Any value in a non-unique key can occur in more than one partition, and there's no conflict.

Note also that in the MySQL source code, in file sql/sql_partition.cc, there's a function check_primary_key(), which has a comment:

This function verifies that if there is a primary key that it contains all the fields of the partition function.
This is a temporary limitation that will hopefully be removed after a while.

So I would infer that there are other ideas about how to implement partitioning without requiring unique indexes be included in the partition function. As in any project, there are more good ideas than engineer-hours, and work must be done by prioritizing the features of most demand.

We connect tables using foreign keys to establish relationships and maintain data integrity within a relational database. This ensures that data in one table is associated with related data in another table, enabling efficient queries, data retrieval, and enforcing referential integrity. By using foreign keys, we create connections between tables that reflect real-world relationships, allowing for the retrieval of meaningful and organized information while preventing data inconsistencies and orphans in the database. This relational structure simplifies data management and allows for cascading updates and deletes when data in the referenced table changes, promoting data accuracy and consistency.

Here is my advice on Not Null for all RDBMS. Every column should have the Not Null constraint unless there is a good reason for allowing null. I.e., the default in your mind should always be Not Null so that you have to think about it and why there can be a Null value. Null values just complicates queries and often the performance of queries. So there should be a reason for Null, otherwise the value should otherwise default when it is not specified.

Here is the kind of thing I see a lot. Excuse my sudo TSQL:

SKU Table:
skuid bigint NOT NULL
count int NULL
UPC nchar NULL
BrandID bigint NOT NULL

OK, it is common for a SKU to not have a UPC so that is OK but the count can be null?? Default it to zero! Please! Otherwise your SQL looks like this when, for example I want to know what SKU's are sold out.

Select skuid from SKU where count is null Or count = 0

Yes, in pretty much any database that uses row-structured storage, a unique constraint creates a secondary index that has to be maintained upon INSERTing, DELETEing, and otherwise dealing with the data.

The only way a DB engine can validate that a column is actually UNIQUE is by checking an index to make sure it’s unique.

Column-structured databases can answer UNIQUE basically “for free”, as this type of information is fundamental to their base storage scheme, particularly if they use some type of bitmap storage.

An aside: if you have a UNIQUE constraint, don’t explicitly add a secondary index on the same column as you’ll just create two indexes on the same thing.

Just like any other update UPDATE tbl_name SET colx = valuex WHERE condition. You just have to be sure that you won’t violate the foreign key constraint when updating and this can happen only if updating the column or columns holding the foreing key constraint. Maybe the foreign key constraint was defined with the ON UPDATE or ON DELETE options. Better if you know rather well the structure of the database you work with. If you were dropped to work with a db you have never used or defined, then study the definition documentation, it should exist! You can always do some retro engineering using the appropriate tools.

Foreign key is used to establish relationship between tables in a relational database.

They have several purposes

Data Integrity: Foreign keys ensure data integrity by enforcing referential integrity constraints. They prevent the insertion of data that doesn't have a corresponding record in the related table, helping maintain data consistency.

Relationships: They define relationships between tables, such as one-to-one, one-to-many, or many-to-many. For example, a foreign key in an "Orders" table can link to a "Customers" table, showing which customer placed each order.

Retrieval of Related Data: Foreign keys allow you to retrieve related data from multiple tables using JOIN operations, making it easier to query and analyze data.

Maintaining Consistency: When you update or delete records in a table with foreign keys, the associated records in related tables can be updated or deleted automatically, maintaining data consistency.

Indexing: Many database systems automatically create indexes on foreign key columns, which can improve query performance when retrieving related data.

In MySQL, you can create a foreign key with check constraints to ensure data integrity. A foreign key enforces referential integrity by ensuring that values in one table match values in another table. Check constraints, on the other hand, enforce rules on data values within a single table. Here's the process for creating a foreign key with check constraints in MySQL:

1. Create the Parent Table: First, create the parent table that will be referenced by the foreign key. This table should contain the primary key that the child table will refer to. For example.

CREATE TABLE Customers ( 
    customer_id INT PRIMARY KEY, 
    customer_name VARCHAR(255), 
    -- Other columns 
); 

Create the Child Table: Next, create the child table that will contain the foreign key column. This column will reference the primary key of the parent table. Also, define the check constraint for the child table. For example:

CREATE TABLE Orders ( 
    order_id INT PRIMARY KEY, 
    customer_id INT, 
    order_date DATE, 
    order_total DECIMAL(10, 2), 
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) 
        REFERENCES Customers (customer_id), 
    CHECK (order_total >= 0) -- Check constraint 
); 

In this example, the Orders table has a foreign key column customer_id that references the customer_id column in the Customers table. Additionally, a check constraint ensures that the order_total is greater than or equal to 0.

Insert Data: Now, you can insert data into both the parent and child tables. The foreign key constraint will ensure that the values in the customer_id column of the Orders table match values in the Customers table's customer_id column.

INSERT INTO Customers (customer_id, customer_name) VALUES (1, 'John Doe'); 
INSERT INTO Orders (order_id, customer_id, order_date, order_total) VALUES (101, 1, '2023-10-01', 100.00); 

Test the Constraints: When you attempt to insert data that violates the foreign key or check constraints, MySQL will raise an error. For example, if you try to insert an order with a non-existent customer:

INSERT INTO Orders (order_id, customer_id, order_date, order_total) VALUES (102, 2, '2023-10-02', 150.00); 

You'll receive an error because customer ID 2 does not exist in the Customers table.

By following these steps, you can create a foreign key with check constraints in MySQL to maintain data integrity and enforce specific rules on data values in your tables.

When a foreign key constraint fails, you cannot insert or update values in one of the related tables because you are violating the referential integrity defined by this constraint. This is the by design behavior of relational data bases. Let’s say that we will define one table kids (idk int, namek varchar(8)) with id unique and one table pets (idp int, namep varchar(8), idk int) and pets(idk) references kids(idk). Let’s suppose both tables are empty. You can insert values in pets on condition to set idk column to null. You cannot insert values into pets defining idk before inserting values in kids. Once you have added values in kids, then you can insert values in pets on condition that pets(idk) have the same value that one of the kids(idk). If you have rows in pets referencing well kids, then you cannot anymore delete or update one of the kids(idk) columns if they matches one of the pets(idk) values. You cannot update pets(idk) column to a value not existing in kids(idk). You can delete at any moment rows in pets whitout problem. You can update pets setting idp to null equally. You can define clauses on delete, on update to a foreign key constraints to indicate the RDBMS what to do in case of modification or deletion of refering or referenced columns.