3 tables and can't figure out how to set up foreign keys


  • I have 3 tables.

    Table1 (ID bigint, val varchar(20)) with ID being the PK

    Table2 (type1 bigint, type2 bigint, Table1ID bigint) with type1 and type2 together defining the primary key

    Table3 (dbname varchar(20), tablename varchar(20), type bigint with dbname and tablename defining the primary key

    I can figure out the foreign key between Table1 and Table2.  But is there a way to create 2 foreign keys to point Table2.type1 back to Table3.type and separately Table2.type2 back to Table3.type where there Table3.type is not and can't be the primary key.

    2012年7月5日 16:55


  • The foreign key relations can only be set up once you have a primary key or at least the unique not null key. 

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    2012年7月5日 17:01
  • Foreign key can be created if the referenced column of parent table is either a Primary key or is of UNIQUE and NOT NULL constraint.

    As you already have primary key defined on other columns in Table3, you can specify Type column in Table3 with UNIQUE and NOT NULL contraints. Then you could go ahead and create individual foreign keys on Type1 and Type2 columns of Table2 pointing to same Type column of Table3.


    2012年7月5日 17:03
  • Below are sample table creation scripts with the specified keys/constraints:

    CREATE TABLE Table1 (ID bigint NOT NULL PRIMARY KEY, val varchar(20))
    CREATE TABLE Table3 (dbname varchar(20) NOT NULL, tablename varchar(20) NOT NULL, Type bigint UNIQUE NOT NULL, PRIMARY KEY (dbname, tablename))
    CREATE TABLE Table2 (type1 bigint NOT NULL, type2 bigint NOT NULL, Table1ID bigint REFERENCES Table1(ID), PRIMARY KEY (type1, type2), FOREIGN KEY (type1) REFERENCES Table3(Type), FOREIGN KEY (type2) REFERENCES Table3(Type)) 
    2012年7月6日 1:40
  • use the concept of composite primary keys and create unique clustered index
    2012年7月6日 5:33
  • Type in Table3 is not unique.  Many different tables can be of the same type.


    2012年7月6日 12:29
  • In this case you need a separate table for the Types where Type field will be unique / primary key.

    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog

    2012年7月6日 12:56
  • So I have to create a table that will never be used so that I can put on foreign keys.  Doesn't that seem wasteful?


    2012年7月6日 13:56
  • The separate Types may not be considered as wasteful as that would act as a master of all the types. That gets then referred in your Table2.

    Also, instead of adding exact type names in Table3, you could refer it from the new "Types" master table.


    2012年7月6日 14:11