Say I have "Table A":
Id | Col A
1 Z
2 I
3 Null
...and n number of tables that have this format:
Id | A_FK | OtherInfo
1 1 "Some info"
2 2 "Some more info"
3 3 "Blah"
...where A_FK is a foreign key reference to the "Table A" Id.
So there is one "Table A", and n number of "Table B's". For these "B" tables, some of them can have any existing value for A_FK (no constraint required). Other "B" tables can only reference an A_FK value where the corresponding record does not have a Null value in Col A.
So my questions are:
Thanks in advance.
In TableA, create a unique constraint on (ID, ColA), In the tables where "where the corresponding record does not have a Null value in Col A.", add ColA column, make sure it is NOT NULL, and have a FK constraint refer to that unique constraint in TableA.
This answers both your questions.