Menu
I'm trying to make a one to one relationship in a MySQL DB. I'm using the InnoDB engine and the basic table looks like this:
CREATE TABLE `foo` (
`fooID` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` TEXT NOT NULL
)
CREATE TABLE `bar` (
`barName` VARCHAR(100) NOT NULL,
`fooID` INT(11) NOT NULL PRIMARY KEY,
CONSTRAINT `contact` FOREIGN KEY (`fooID`) REFERENCES `foo`(`fooID`)
)
Now once I have set up these I alter the foo table so that the fooID also becomes a foreign key to the fooID in bar. The only issue I am facing with this is that there will be a integrity issue when I try to insert into either. I would like some help, thanks.
501
A one-to-one relationship is a link between the information in two tables, where each record in each table only appears once.
MySQL/MariaDB does not contain any special options to define the one-to-one relationship, but you can obtain one-to-one doing this: add primary keys in both tables; in the second table add a foreign key to point to the primary key from the first table and as constraints add it as unique.
One-to-one relationships associate one record in one table with a single record in the other table. One-to-many relationships associate one record in one table with many records in the other table. Save this answer.
A one-to-one relationship exists when each row in one table has only one related row in a second table. For example, a business might decide to assign one office to exactly one employee. Thus, one employee can have only one office. The same business might also decide that a department can have only one manager.
A true one-to-one relationship in a relational database is best done by adding a column; If the data in these two tables is always required for every record, then the table should be defined as foo (fooID, name, barname).
121
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
