INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

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

(OP)
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;

CODE

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()
) 

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

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


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

(OP)
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.


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

>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.

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

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

CODE

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

CODE

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

CODE

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)


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.


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.


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


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.

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

(OP)
Any insight?

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

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

(OP)
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?

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

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close