Multiple yet mutually exclusive foreign keys - is this the way to go?
I have three tables: Users, Companies and Websites. Users and companies have websites, and thus each user record has a foreign key into the Websites table. Also, each company record has a foreign key into the Websites table.
Now I want to include foreign keys in the Websites table back into their respective "parent" records. How do I do that? Should I have two foreign keys in each website record, with one of them always NULL? Or is there another way to go?
Relational Database Design and Implementation: Clearly Explained, Keys, 91–92 concatenated, 91–92, 176 foreign. See Foreign keys primary. 331 Mandatory relationships, 67–68 Many-to-many relationships, 67 complex network Multiversion concurrency control, 318–319 Mutually exclusive relationships, So my plan was to link each field in the main table to the field in the secondary table with two different foreign key relationships. For example: Table1.A ---> Table2.C As FK_A_C
you don’t need a parent column, you can lookup the parents with a simple select (or join the tables) on the users and companies table. if you want to know if this is a user or a company website i suggest using a boolean column in your websites table.
Is it a bad practice to have several mutually exclusive one-to-one , This can be baffling at first, but one gets used to it. How many distinct predicates do you have in your sub-types? The situation could get complicated. You can do classification with codes, foreign keys to code tables, SQL FOREIGN KEY Constraint. A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
Why do you need a foreign key from website to user/company at all? The principle of not duplicating data would suggest it might be better to scan the user/company tables for a matching website id. If you really need to you could always store a flag in the website table that denotes whether a given website record is for a user or a company, and then scan the appropriate table.
Data Modeling Essentials, derivable relationships, 347–348 one-to-many relationship implementation, 348–350 overlapping foreign keys, 350–352 overview, 342–343 split foreign keys, 215–216, 337–339 multivalued dependency (MVD), 401 mutually exclusive 397–398 example of structure in 3NF but not in BCNF, 394–396 overview, 394 Place foreign keys on the child tables back to the master using (email, type) This leads to several "redundant" columns and constraints. On 11g and higher you can reduce storage overhead by using virtual columns for the type columns on the child tables.
The problem I have with the accepted answer (by Quassnoi) is that the object relationships are the wrong way around: company is not a sub-type of a website owner; we had companies before we had websites and we can have companies who are website owners. Also, it seems to me that website ownership is a relationship between a website and either a person or a company i.e. we should have a relationship table (or two) in the schema. It may be an acceptable approach to keep personal website ownership separate from corporate website ownership and only bring them together when required e.g. via
CREATE TABLE People ( person_id CHAR(9) NOT NULL UNIQUE, -- external identifier person_name VARCHAR(100) NOT NULL ); CREATE TABLE Companies ( company_id CHAR(6) NOT NULL UNIQUE, -- external identifier company_name VARCHAR(255) NOT NULL ); CREATE TABLE Websites ( url CHAR(255) NOT NULL UNIQUE ); CREATE TABLE PersonalWebsiteOwnership ( person_id CHAR(9) NOT NULL UNIQUE REFERENCES People ( person_id ), url CHAR(255) NOT NULL UNIQUE REFERENCES Websites ( url ) ); CREATE TABLE CorporateWebsiteOwnership ( company_id CHAR(6) NOT NULL UNIQUE REFERENCES Companies( company_id ), url CHAR(255) NOT NULL UNIQUE REFERENCES Websites ( url ) ); CREATE VIEW WebsiteOwnership AS SELECT url, company_name AS website_owner_name FROM CorporateWebsiteOwnership NATURAL JOIN Companies UNION SELECT url, person_name AS website_owner_name FROM PersonalWebsiteOwnership NATURAL JOIN People;
The problem with the above is there is no way of using database constraints to enforce the rule that a website is either owned by a person or a company but not both.
If we can assuming the DBMS enforces check constraints (as the accepted answer does) then we can exploit the fact that a (human) person and a company are both legal persons and employ a super-type table (
LegalPersons) but still retain relationship table approach (
WebsiteOwnership), this time using the
VIEWs to separate personal website ownership from separate from corporate website ownership but this time with strongly typed attributes:
CREATE TABLE LegalPersons ( legal_person_id INT NOT NULL UNIQUE, -- internal artificial identifier legal_person_type CHAR(7) NOT NULL CHECK ( legal_person_type IN ( 'Company', 'Person' ) ), UNIQUE ( legal_person_type, legal_person_id ) ); CREATE TABLE People ( legal_person_id INT NOT NULL legal_person_type CHAR(7) NOT NULL CHECK ( legal_person_type = 'Person' ), UNIQUE ( legal_person_type, legal_person_id ), FOREIGN KEY ( legal_person_type, legal_person_id ) REFERENCES LegalPersons ( legal_person_type, legal_person_id ), person_id CHAR(9) NOT NULL UNIQUE, -- external identifier person_name VARCHAR(100) NOT NULL ); CREATE TABLE Companies ( legal_person_id INT NOT NULL legal_person_type CHAR(7) NOT NULL CHECK ( legal_person_type = 'Company' ), UNIQUE ( legal_person_type, legal_person_id ), FOREIGN KEY ( legal_person_type, legal_person_id ) REFERENCES LegalPersons ( legal_person_type, legal_person_id ), company_id CHAR(6) NOT NULL UNIQUE, -- external identifier company_name VARCHAR(255) NOT NULL ); CREATE TABLE WebsiteOwnership ( legal_person_id INT NOT NULL legal_person_type CHAR(7) NOT NULL UNIQUE ( legal_person_type, legal_person_id ), FOREIGN KEY ( legal_person_type, legal_person_id ) REFERENCES LegalPersons ( legal_person_type, legal_person_id ), url CHAR(255) NOT NULL UNIQUE REFERENCES Websites ( url ) ); CREATE VIEW CorporateWebsiteOwnership AS SELECT url, company_name FROM WebsiteOwnership NATURAL JOIN Companies; CREATE VIEW PersonalWebsiteOwnership AS SELECT url, person_name FROM WebsiteOwnership NATURAL JOIN Persons;
What we need are new DBMS features for 'distributed foreign keys' ("For each row in this table there must be exactly one row in one of these tables") and 'multiple assignment' to allow the data to be added into tables thus constrained in a single SQL statement. Sadly we are a far way from getting such features!
Constraint for multiple foreign key yet it's mutually exclusive , As it sounds, you cannot solve this with one constraint, but nothing prevents you from defining more of them. I believe something like this should A composite key specifies multiple columns for a primary-key or foreign-key constraint. The next example creates two tables. The first table has a composite key that acts as a primary key, and the second table has a composite key that acts as a foreign key.
First of all, do you really need this bi-directional link? It is a good practice to avoid it unless absolutely needed.
I understand it that you wish to know whether the site belongs to a user or to a company. You can achieve that by having a simple boolean field in the Website table - [BelongsToUser]. If true, then you look up a user, if false - you look up a company.
Implementing a Mutually Exclusive Relationship in the Database , Enforce the mutually exclusive relationship using database There is no simple answer for how to do this. The type columns enable you to enforce the rule with foreign keys, Mutually exclusive relationships come in many forms. Not to be a nitpicker, but the gif looks like an antijoin of 2 overlapping multiple foreign keys? I tried looking up that information in the SQL books but didn't find anything on it. How would you model an AND relationship with multiple foreign keys if that is the case? (Just curious) I think Hugo's made a mistake his table. I would be very difficult to insert anything at all in BidFolder with that design! This is
Data Integrity, This chapter explains how integrity constraints enforce the business rules associated with a database and Foreign keys may be defined as multiple columns. Foreign key can be a duplicate.The basic topic in this article is Difference between Primary Key and Foreign Key.In this article i will first explain you the difference between primary key and foreign key in multiple bullet-points with real examples.Then I will explain the difference in to tabular format which gives the correct idea to the users.
Documentation: 12: 5.4. Constraints, Data types are a way to limit the kind of data that can be stored in a table. For many applications, however, the constraint they provide is too coarse. (PostgreSQL doesn't enforce that rule, but you should follow it if you want your table If possible, use UNIQUE , EXCLUDE , or FOREIGN KEY constraints to express It is strange that one can ask simple questions about extended events or Hekaton at professional events and conferences without feeling embarrassed, yet nobody likes to ask vital questions about SQL Server primary keys and foreign keys. Once more, Rob Sheldon is 'drawn to one side' to answer those questions about keys that one is too shy to ask.
Setting Up Referential Integrity, Multiple foreign keys can reference the same primary key. cascade rule on the same keys, because the two behaviors are mutually exclusive. you must have rows in Table A before you can add any rows in Table B. Stated another way, In most cases you can get “best of both worlds” access to your database, but since Use this activity to practice calculating probabilities of mutually exclusive events and non-mutually exclusive events. This 12 question room walk activity contains a student activity sheet, 11" x 8.5" question posters, and an answer key. Post the questions around the room and watch as your studen
- "force the correct type in the child tables with a CHECK constraint" - these are not enforced in mySQL, right?
- @onedaywhen: no, they're not.
- +1 - pretty well exactly what I said, so I clearly think you're right!