Allow my to elaborate on the question with an example. I am writing from the perspective that
(1) bytecodes should not be used to implement the logic already implemented (hopefully more efficiently) in the database engine (e.g., if we need to filter out 20 lines out of 500 coming out as the result of an SQL query, we should be writing a better where clause), and that
(2) I have only a conceptual understanding of foreign keys (e.g., they automatically create, manage and enforce the constraints required to maintain data integrity across different tables).
Now, lets consider a simple schema with 4 tables and 15 columns in them as follows (assume all columns are not null):
people pid bigint autoinc PK fname varchar(32) lname varchar(32) dob date sex char(1) addyID bigint FK to address(aid) relationship relof bigint FK to people(pid) relto bigint FK to people(pid) relis tinyint address aid bigint autoinc PK street varchar(128) zipcode int FK to z2cs(zip) z2cs zip int PK city varchar(64) state char(2)
Expectations (Please answer True/False to the following 6)
- It is possible to create the table relationship as above with foreign keys pointing to different rows of the same table
- Adding a row to relationship throws SQLException if either relof or relto is missing in people
- If we add a row to address, we do not need to check if the zipcode is there in z2cs - missing zip throws an SQLException
- Attempt to delete a row from address throws an exception if aid is used in people
- Attempt to delete a row from people causes error if pid is used in relationship in either of the columns relto or relof
- An SQL statement exists that lets you find which columns in a table are foreign keys and which table(column) do they refer to (I know "describe tablename" doesn't do it, although I feel that it should)
If the answer to any of the above is false, can you please guide me to finding what exactly is the contract for foreign keys in MySql. Thank you.
PS: If you answer True to , would you please share what is that statement.
Bonus question (unrelated to FK):
How would you write the create statement for the relationship table that satisfies the following constraints:
- relof and relto cannot be the same
- Combination of relof and relto is unique, i.e., if their is a row with [relof=584,relto=7823], you cannot insert another row with either [relof=584,relto=7823] or [relof=7823,relto=584]