Having issues with creating a FK

Question

Having issues with creating a FK

NewBornDBA2017

SSCarpal Tunnel

Points: 4832
More actions
July 19, 2017 at 9:59 am

#338359

I am wondering if I can get some help on the issue I am running into. I am creating a table and I have to create a FK constraint on Company ID which refers to a comapny ID in another table. The problem is that all the columns we have space between name. Please look at the code for better understanding.This is the base table CREATE TABLE [Company]( [Company Title] [varchar](150) NULL, [Company Id] [varchar](10) NULL, [Company Headquarters] [varchar](100) NULL, [Company Email] [varchar](150) NULL, [Company Website] [varchar](150) NULL, [Company Phone and Fax] [varchar](2000) NULL, [Company Address Line 1] [varchar](150) NULL, [Company Address Line 2] [varchar](150) NULL, [Company State] [varchar](150) NULL, [Company Country] [varchar](50) NULL, [Company City] [varchar](50) NULL, [Company Postal Code] [varchar](255) NULL, )
This is the new table I am creating but unable to create a FK since Comapny ID has a space between company and id
create table dm.DimCompany ( DimCompanyKey int IDENTITY(1,1) Primary Key ,[CompanyId] int foreign key references Company (Company Id) )

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

your Table structure has all the columns in brackets, so that the name with spaces doesn't break the CREATE TABLE command..
your Foreign keys simply need to have the same logic.

you also need to identify the schema of the table in question, if it is not dbo.

create table [dm].[DimCompany] ( [DimCompanyKey] int IDENTITY(1,1) Primary Key ,[CompanyId] int foreign key references [UnknownSchema].[Company] ([Company Id]) )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

In addition to what Lowell said, the data types of your columns don't match.

newdba2017 - Wednesday, July 19, 2017 9:59 AM
This is the base table CREATE TABLE [Company](     [Company Title] [varchar](150) NULL,     [Company Id] [varchar](10) NULL,...
create table dm.DimCompany
(
    DimCompanyKey int IDENTITY(1,1) Primary Key
    ,[CompanyId] int foreign key references Company (Company Id)
   )

Your Company table has it defined as a Varchar(10) and your DimCompany table has it as an integer.

Also, consider making Companies.[Company Id] the primary key for that table (after changing the data type).

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions