Is there a way to have foreign keys without the index and integrity constraint. I would love to be able to keep the relational mapping at the db layer. (I almost always actually want the integrity and indedx, but I'm just curious if its possible)
Yes, just have columns for all the primary key fields of the referred-to table in the dependent table.
Then it's up to all your apps to guarantee that they fill those fields in correctly, and do the reference check when inserting or updating rows in the dependent table.
You also need to write checking programs that report foreign key errors on a timetable that suits (every minute, every hour, every day, whattever).
Report the errors to someone who understands the significance of them, and can fix them.
What I'm specifically looking for is a foreign key relation so that you could for example still generate a chart of relations. For example an audit log you wouldn't want the consistency check, but it would still be nice to know it links to X tables.
In the RDBMSes I'm aware of, you can disable foreign key checks while still adding the constraints themselves---they won't do anything as far as the DBMS is concerned, but can presumably still be picked up by whatever tool you are using to generate this link information.
You usually can for maybe a table, or just overall. But I don't think you can specifically for one FK. This is mostly just a small pipe dream I think :')
No, in all RDBMSes I know of, it's absolutely the other way around: Enabled or disabled (usually CHECKED / NOT CHECKED) is an attribute you set for each and every constraint, either at creation (where it usually has one or the other as default if you don't explicitly set it) or in an ALTER... statement.
