TSQL - Create Foreign Keys - Error: There is already an object named FK_EquipMaster" 2

[BxWill]

Insight needed.

Upon attempting to create a foreign key on the second table in the database, I receive the following error;

"There is already an object named 'FK_EquipMaster' in the database."

When creating multiple tables using CREATE TABLE and specifying foreign key constraints, should each successive CREATE TABLE block contain a different name for the foreign key constraint?

Thanks in advance.


The code is as follows;

USE master ;  
GO  
DROP DATABASE EquipmentLocation ;  
GO  

CREATE DATABASE EquipmentLocation
go
 
Use EquipmentLocation
Go	

CREATE TABLE #EquipMaster (	
	[JurisdictionCode] [varchar] (60 ) NULL,
	[AccountNo] [varchar] (60 ) NULL,
	[SequenceNbr] [varchar] (60 ) NULL,
	[SaleAmount] [varchar] (60 ) NULL,
	[SaleDate] [varchar] (60 ) NULL,
        [TaxAmount] [varchar] (60 ) NULL,
)
.
.
.

CREATE TABLE EquipMaster (	
	[JurisdictionCode] [varchar] (5) NOT NULL,
	[AccountNo] [varchar] (45) NOT NULL,
	[SequenceNbr] int NOT NULL,
	[SaleAmount] int NULL,
	[SaleDate] date NULL,
        [TaxAmount] int NULL,
        Constraint PK_EquipMaster Primary Key ([JurisdictionCode], [AccountNo], [SequenceNbr])
        --CreatedBy varchar (255) default system_user
        --CreatedDate date NULL Default GETDATE()
)
.
.
.

CREATE TABLE #EquipmentData1 (	
	[JurisdictionCode] [varchar] (60 ) NULL,
	[AccountNo] [varchar] (60 ) NULL,
	[SequenceNbr] [varchar] (60 ) NULL,
	[Zipcode] [varchar] (60 ) NULL,
	[SaleAmount] [varchar] (60 ) NULL,
	[SaleDate] [varchar] (60 ) NULL,
)	

CREATE TABLE [Equipment_2016](		
	[id] int identity (1,1) NOT NULL primary key,
        [JurisdictionCode] [varchar] (5 ) NOT NULL,   
	[AccountNo] [varchar] (45 ) NOT NULL,      
	[SequenceNbr] int NOT NULL,              
	[Zipcode] [varchar] (9) NULL, 
	[SaleAmount] int NULL,
	[SaleDate] date NULL, ---format is (YYYYMMDD)
	constraint FK_EquipMaster FOREIGN KEY ([JurisdictionCode], [AccountNo], [SequenceNbr]) REFERENCES EquipMaster ([JurisdictionCode], [AccountNumber], [SequenceNbr]	
        --CreatedBy varchar (255) default system_user
        --CreatedDate date NULL Default GETDATE()
)

.
.
.
BULK INSERT #EquipmentData1
   	FROM 'C:\EquipmentLocation\Data\201706EquipmentData.txt'    
   WITH
      (
         FIELDTERMINATOR = '\t',
		 ROWTERMINATOR = '\n',
		 FIRSTROW = 2
	 )

.
.
.
CREATE TABLE [Equipment_2017](		
	[id] int identity (1,1) NOT NULL primary key,
        [JurisdictionCode] [varchar] (5 ) NOT NULL,   
	[AccountNo] [varchar] (45 ) NOT NULL,      
	[SequenceNbr] int NOT NULL,              
	[Zipcode] [varchar] (9) NULL, 
	[SaleAmount] int NULL,
	[SaleDate] date NULL, ---format is (YYYYMMDD)
	constraint FK_EquipMaster FOREIGN KEY ([JurisdictionCode], [AccountNo], [SequenceNbr]) REFERENCES EquipMaster ([JurisdictionCode], [AccountNumber], [SequenceNbr]	
        --CreatedBy varchar (255) default system_user
        --CreatedDate date NULL Default GETDATE()
)

 

[johnherman]

As the error states, each database object has a unique identifier (name). Thus for table Equipment_2017, something like:
constraint FK_EquipMaster_2017 FOREIGN KEY

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity

 

[BxWill]

Noted.

It appears that there is a difference between assigning a user-defined name to the foreign key versus allowing a system-generated name to the foreign key.

Any further insight as to the creation of the two fields - "CreatedBy" and "CreatedDate" fields?

Did attempt to create but was not successful.

 

[Olaf Doschke]

>It appears that there is a difference between assigning a user-defined name to the foreign key versus allowing a system-generated name to the foreign key
No, if you look closely system generated names are unique.

In regard to CreatedBy and CreatedDate fields, don't mix up fields, then constraints, then again fields, even just for the sake of overview and maintainability.

Bye, Olaf.

 

[BxWill]

Encountered Error - "Invalid Column Names" when using the Insert into portion of the tSQL script to load the Equipment_2016 table.

insert into Equipment_2016
select distinct
RTRIM(LTRIM([Jurisdiction_Code])),
RTRIM(LTRIM([AccountNo])),
RTRIM(LTRIM([SequenceNo]))
.
.
.
)
from #EquipmentData4
where 
[Sale_Date] between convert(date, '2016-01-01') and convert(date, '2016-12-31')  
AND RTRIM(LTRIM([Sale_Amount])) is not null
AND RTRIM(LTRIM([Sale_Date])) is not null 
.
.
.

Note, the first bulk insert statement

BULK INSERT #EquipmentData1
   	FROM 'C:\EquipmentLocation\Data\Equipment_InitialLoad.txt'    
   WITH
      (
         FIELDTERMINATOR = '\t',
		 ROWTERMINATOR = '\n',
		 FIRSTROW = 2
	 )

Note, the bulk insert statement that is used to populate the temporary file for loading the Nov 2016 data

BULK INSERT #EquipmentData4
   	FROM 'C:\EquipmentLocation\Data\201611_EquipmentData.txt'    
   WITH
      (
         FIELDTERMINATOR = '\t',
		 ROWTERMINATOR = '\n',
		 FIRSTROW = 2
	 )

Context:

Note, there are multiple text files that I need to load from location C:\EquipmentLocation\Data\

Equipment_InitialLoad.txt (Contains multiple year's data from Jan 2014 thru Oct 2016)
201611_EquipmentData.txt
201612_EquipmentData.txt
201701_EquipmentData.txt
.
.
.
201706_EquipmentData.txt


To load each text file, I perform the following;

create a temp table
create db table
bulk insert from text file into temp table
Insert into db table from temp table (after validating data)

[Bold]
Apparently, the inclusion of the auto-incrementing id field in the tables (Equipment_2014, Equipment_2015, Equipment_2016, Equipment_2017) appears to be the source of the error.

There are fewer columns in the INSERT statement than fields within the tables.[/Bold]

So, to populate the tables Equipment_2014 and Equipment_2015, it appears that I can still use the temporary table #EquipmentData1. It also appears that I can populate table Equipment_2016 with data from Jan 2016 through Oct 2016 using the same temporary table, #EquipmentData1.

[Bold]
However, I encounter the "Invalid Column Name" error when creating another temp table and using bulk insert to load the data for November 2016.
[/Bold]

Currently exploring the following possible resolutions;

* Create a view and use bulk insert against it
* or, Scrap the use of multiple bulk inserts and implement the MERGE functionality to load multi-year data
* or, Continue to edit the pre-existing TSQL script - Inserting "GO", etc.
* or, Add id field to the text file using a text editor
* or, using tSQL, alter temp tables by adding identity field?

As this is the first Sql Server database that I am constructing, I appreciate any insight as to a possible resolution of the error.

 

[BxWill]

Any insight?

At this point, it appears that eliminating the auto incrementing id field may be a viable option...

 

[Olaf Doschke]

Does your flat file data have values for the identity column? Then these are the options to import them:

https://jingyangli.wordpress.com/20...ert-value-into-identity-column-in-sql-server/

Bye, Olaf.

 

[BxWill]

Flat file does not contain an id column.

It appears that the error may be attributable to changing the name of the temporary table withim the "bulk insert" and "insert into" sections of the sql script.

Is it possible/feasible to use the same trmporary table to load the 2014, 2015 and 2016 data (data from Jan 2016 through Oct 2016) and then a different temporary table to load the data for Nov 2016, another temporary table to load Dec 2016, a different temporary tsble for Jan 2017, and so on?

After use of a temporary table, csn it be truncated, dropped and re-used?

Or, is the present setup preferred?

What is best practice when loading multiple text files that contain monthly data?

Is it possible to load a db table with data from more than one text file? In other words, say if there is June 2017 data in the text files labeled 201706-Euipment and the file 201705-Equipment that is within the same folder?

 

[Olaf Doschke]

If you generate IDs on the fly, all ids must be generated on the same table or of course you get double values. One way avoiding that is to create identity fields with start value depending on the last final value, if you want to work with multiple temp tables. But in the end the target table has to accept what is inserted into it or has to be the only table generating IDs.

As far as I understand the identity value is not the only column for the primary key, so you should think about whether it really makes sense to use such an identity column or define a stored proc to define counters per year, accountno, whatever combination of values having doubles and needing an additional sequence number starting from 1 per group.

The other simple option is to merely use identity as the only primary key value, this will not test other real columns for uniqueness, but porimary keys are not the only unique keys, you can put a unique constraint on any comination of columns as you like even as normal indexes.

Last not least a GUID (uniqueidentifier) surely is the simplest solution to avoid such key generation problems.

Bye, Olaf.