Create a sql foreign key constraint

Question

I wish to create a constraint that state as below

Code.CodeTable ( CodeID smallint, CategoryID smallint,....)  --> Parent Table
Admin.Document( DocumentTypeID smallint,.....)  --> Child Table

The FK will be Admin.Document.DocumentTypeID map with Code.CodeTable.CodeID

I wish to have the constraint that only check Code.CodeTable.CodeID which the Code.CodeTable.CategoryID = 15 only.

You can't make a foreign key constraint across databases, or is that your naming convention--to put periods in the table name? If that is your table naming convention, I don't recommend it. — OMG Ponies
– OMG Ponies, Commented Sep 14, 2010 at 4:38
@OMGPonies: the OP is referring to a schema with the period and not another database. — jmoreno
– jmoreno, Commented Oct 4, 2013 at 18:16

marc_s · Accepted Answer · 2010-09-14 05:09:35Z

4

As OMG Ponies already said - you cannot create fk constraints across databases, but if those are just odd table names with dots in them (highly discouraged! since SQL Server already uses a dotted schema: (database).(schema).(object name) and thus having dots in your table names is just asking for trouble at some point....), then you should be able to create your constraint like this:

ALTER TABLE [Admin.Document]
  ADD CONSTRAINT FK_AdminDocument_CodeTableCodeID
  FOREIGN KEY(DocumentTypeID) REFERENCES [Code.CodeTable](CodeID)

Since you have dots in your table names, you need to enclose those names in square brackets [].

Basically, you need to modify the child table and tell SQL Server which column in that child table refers to what parent table and column in the parent table.

edited Sep 14, 2010 at 5:09

answered Sep 14, 2010 at 4:54

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create a sql foreign key constraint

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related