CODEWITHSUNDEEP
sqlsql-serverforeign-keys
SeReGa
SeReGa

Reputation: 1317

SQL Server - set column_id as a Foreign Key

I Have 2 tables:

Table A with the columns:

 Table A
 ---------
 ID
 Name
 Address

Table B with the columns:

 Table B
-------
 ID
 A_ColumnIDs

I want to set to B.A_ColumnIDs a foreign key to A table's column_ids.

i.e. the valid values for the column B.A_ColumnIDs are: 1,2,3 as the column_ids of the columns.

I have a script to take the id and the name of the columns:

    SELECT 
        C.name ColumnName,
        C.column_id ColumnID
    FROM sys.tables T
        INNER JOIN sys.columns C
            ON T.object_id = C.object_id
    WHERE C.object_id = OBJECT_ID('A')

The result of this script is:

 ColumnName | ColumnID
-----------------------
 ID         |    1
 Name       |    2
 Address    |    3

Now I want the ColumnID to be the foreign key of B.A_ColumnIDs

But I don't know how to set the foreign key to it.

The story behind the request:

I need to be able to restrict the values I get in B.A_ColumnIDs, by the existing columns in A, and if a column is added / removed / renamed in A, I want to stay synchronized.

Upvotes: 0

Views: 181

Answers (1)

GuidoG
GuidoG

Reputation: 12014

I am not sure if I understand you correct, but I think you want this

create table A (
  Id int not null identity,
  Name varchar(100),
  Address varchar(100),
  ...

  constraint PK_AId primary key (Id)
)

create table B (
  Id int not null identity,
  A_Id int not null,
  other columns...,

  constraint PK_BId primary key (Id),
  constraint FK_B_A foreign key (A_Id) references A (Id)
)

now you have a relation between both tables, where B can have multiple rows connected to one row in A

Is this what you want ?

Upvotes: 1

Related Questions