Tsql Foreign Keys on Views

Can I have a foreign key referencing a column in a view in SQL Server?

You can't reference a view in a foreign key.

TSQL foreign keys on views?

Peter already hit on this, but the best solution is to:

1. Create the "main" logic (that filtering the referenced table) once.
2. Have all views on related tables join to the view created for (1), not the original table.

I.e.,

CREATE VIEW v1 AS SELECT * FROM table1 WHERE blah

CREATE VIEW v2 AS SELECT * FROM table2 WHERE EXISTS
  (SELECT NULL FROM v1 WHERE v1.id = table2.FKtoTable1)

Sure, syntactic sugar for propagating filters for views on one table to views on subordinate tables would be handy, but alas, it's not part of the SQL standard. That said, this solution is still good enough -- efficient, straightforward, maintainable, and guarantees the desired state for the consuming code.

How can I find out what FOREIGN KEY constraint references a table in SQL Server?

Here it is:

SELECT 
   OBJECT_NAME(f.parent_object_id) TableName,
   COL_NAME(fc.parent_object_id,fc.parent_column_id) ColName
FROM 
   sys.foreign_keys AS f
INNER JOIN 
   sys.foreign_key_columns AS fc 
      ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN 
   sys.tables t 
      ON t.OBJECT_ID = fc.referenced_object_id
WHERE 
   OBJECT_NAME (f.referenced_object_id) = 'YourTableName'

This way, you'll get the referencing table and column name.

Edited to use sys.tables instead of generic sys.objects as per comment suggestion.
Thanks, marc_s

Create a view with multiple foreign key referencing a single field

This is a common reporting pattern wherever the database architect has employed the "one true lookup table" model. I'm not going to get bogged down in the merits of that design. People like Celko and Phil Factor are far more erudite than me at commenting on these things. All I'll say is that having reported off over sixty enterprise databases in the last 15 years, that design is pervasive. Rightly or wrongly, you're probably going to see it over and over again.

There is currently insufficient information to definitively answer your question. The answer below makes assumptions on what I think is the most likely missing information is.

1. I'll assume your product table is named PRODUCT
2. I'll assume your all-powerful lookup table is call REFS
3. I'll assume RefCodeKey in REFS has a unique constraint on it, or it is the a primary key
4. I'll assume the REFS table is relatively small (say < 100,000 rows). I'll come back to this point later.

5. I'll assume that the foreign keys in the PRODUCT table are nullable. This affects whether we INNER JOIN or LEFT JOIN.

   SELECT prod.PC
          ,prod.PN
          ,reg_code.label  as RCKey
          ,prod_stat.label as PSKey
          ,prod_clas.label as PCKey
   FROM   PRODUCT             prod
          LEFT JOIN REFS  reg_code ON prod.RCKey = reg_code.RefCodeKey
          LEFT JOIN REFS prod_stat ON prod.PSKey = prod_stat.RefCodeKey
          LEFT JOIN REFS prod_clas ON prod.PCKey = prod_clas.RefCodeKey
   ;
   

The trick is that you can refer to the REFS table as many times as you like. You just need to give it a different alias and join it to the relevant FK each time. For example reg_code is an alias. Give your aliases meaningful names to keep your code readable.

Note: Those RCKey/PSKey/PCKey names are really not good names. They'll come back to bite you. They don't represent the key. They represent a description of the thing in question. If it's a region code, call it region_code

The reason I'm assuming the REFS table is relatively small, is that if it's really large (I've seen one with 6 million lookup values across hundreds of codesets) and indexed to take RefCodeType into consideration, you might get better performance by adding a filter for RefCodeType to each of your LEFT JOINs. For example:

       LEFT JOIN REFS prod_clas ON prod.PCKey = prod_clas.RefCodeKey
                                   AND prod_clas.RefCodeType = 'ProductClassificationKey' 

How can I list all foreign keys referencing a given table in SQL Server?

Not sure why no one suggested but I use sp_fkeys to query foreign keys for a given table:

EXEC sp_fkeys 'TableName'

You can also specify the schema:

EXEC sp_fkeys @pktable_name = 'TableName', @pktable_owner = 'dbo'

Without specifying the schema, the docs state the following:

If pktable_owner is not specified, the default table visibility rules
of the underlying DBMS apply.

In SQL Server, if the current user owns a table with the specified
name, that table's columns are returned. If pktable_owner is not
specified and the current user does not own a table with the specified
pktable_name, the procedure looks for a table with the specified
pktable_name owned by the database owner. If one exists, that table's
columns are returned.

Create view with two foreign keys from the same table

You will have to join your EMP table two times for different conditions

Alter VIEW Tests AS
SELECT Dept_Name, EMP1.Emp_Name, EMP2.Emp_Name AS Dept_Boss 
FROM Dept
JOIN EMP EMP1 on Dept_Emp = EMP1.Emp_ID 
JOIN EMP EMP2 on Dept_Boss = EMP2.Emp_ID 

SQL Server - view all foreign key dependencies

select OBJECT_NAME(parent_object_id), OBJECT_NAME(referenced_object_id)
    from sys.foreign_keys
    where referenced_object_id = object_id('SchemaName.TableName')

Django - Existing DB Views and Foreign Keys

Since the view is not actually a table, you cannot set Foreign Key constraints. Since ForeignKey's default db_constraint value is True, Django tries to set Foreign Key constraints when performing migrations. This is the reason the migration fails.

So, you can turn off the db_constraint option. And you can remove the existing migration file, and re-create the migration file. Then, the migration will success and you can keep everything in sync.

class PricePlanDownload(models.Model):
    ... other fields ...
    priceplan = models.ForeignKey(AutPricePlanView, null=True, on_delete=models.DO_NOTHING, db_constraint=False)

Pro Tip: You can review migration's SQL using python manage.py sqlmigrate <appname> <migration number>, like python manage.py sqlmigrate yourapp 0002.

---

Update: You can define __str__ to display the correct value at the dropdown menu.

class AutPricePlanView(models.Model):
    priceplan_name = models.CharField(db_column='PricePlan', max_length=50, blank=True, unique=True, primary_key=True)  
    # null=False by default. See https://github.com/django/django/blob/master/django/db/models/fields/__init__.py#L132

    def __str__(self):
        return self.priceplan_name

    class Meta:
        managed = False  # Created from a view. Don't remove.
        db_table = 'AUT_PricePlanView'

---

Related Topics