July 14, 2014 at 2:40 am
I have 2 tables. I want to link a column in each together with a foreign key using an SQL command. Remember the tables already exist. How do you do this?
July 14, 2014 at 2:53 am
As a start, you can look up JOINS in Google or Books Online.
Please provide the DDL of both the tables involved and the names of columns you would like to join.
Some sample data and expected results would help us give you a tested solution back.
Please check the link in my sgnature if you are not sure how to provide the needfull.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 14, 2014 at 4:33 am
You just need to run an ALTER TABLE script. Here's an article from Microsoft on how to do it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2014 at 5:27 am
I am trying to get my head around this Foreign Key stuff.
I have a table: Employees with a column: DepartmentID.
I have another table: Departments with a column: ID that is the Primary Key.
I want to link ID and DepartmentID together using a Foreign Key.
I am a little confused about "FK_Employees". Does that just refer to Employees?
Does the Primary Key mess thing up with the order of things?
ALTER TABLE Departments ADD CONSTRAINT FK_Employees FOREIGN KEY (DepartmentID) REFERENCES Department(ID);
July 14, 2014 at 5:42 am
The primary key in Departments is the way to uniquely identify each row in the table. When you create a foreign key in Employees on DepartmentID, you are telling SQL that the DepartmentID column points to the primary key in Employees.
This means that you cannot populate a value in Employees.DepartmentID that does not match a value in Departments.ID. You are enforcing what's called Referential Integrity, which is a good term to look up. It enforces the integrity of the data so you don't have employees linked to invalid departments.
July 14, 2014 at 5:52 am
The name on a foreign key can be pretty much whatever you choose to define it as. Usually, I'd have it be descriptive, like DepartmentEmployeeFK or something similar, so that it shows what is related to what. There's nothing about the name that defines behavior, it's just the column or columns that you link together between tables.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 14, 2014 at 11:34 am
philosophaie (7/14/2014)
I am trying to get my head around this Foreign Key stuff.I have a table: Employees with a column: DepartmentID.
I have another table: Departments with a column: ID that is the Primary Key.
I want to link ID and DepartmentID together using a Foreign Key.
I am a little confused about "FK_Employees". Does that just refer to Employees?
Does the Primary Key mess thing up with the order of things?
ALTER TABLE Departments ADD CONSTRAINT FK_Employees FOREIGN KEY (DepartmentID) REFERENCES Department(ID);
I believe the FK creation should be:
ALTER TABLE dbo.Employees
ADD CONSTRAINT FK_Department --this name is arbitrary, but typically will
--refer to the referenced table, not the referencing table
FOREIGN KEY ( DepartmentID )
REFERENCES Department ( ID ) ;
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
July 14, 2014 at 11:57 am
I know the name has no functional impact on anything, but I prefer the naming convention that includes the referencing table and the referenced table. Example: Employees_Departments_FK. This way, if I look at a query of the system tables or see the name of the foreign key, I know immediately where it is and what it references.
Everyone has their own preferences on naming conventions and there's really no absolute right way to do it.
July 14, 2014 at 12:31 pm
Ed Wagner (7/14/2014)
I know the name has no functional impact on anything, but I prefer the naming convention that includes the referencing table and the referenced table. Example: Employees_Departments_FK. This way, if I look at a query of the system tables or see the name of the foreign key, I know immediately where it is and what it references.Everyone has their own preferences on naming conventions and there's really no absolute right way to do it.
Quite true.
My personal preference is for "basetable__FK_base_column". For example: "Employees__FK_DepartmentID". Likewise I use "__CL", "__IX_...", "__CK_...", etc.. Many different preferences out there.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply