Truncate Tables Based On Foreign Key Relationships
Nov 5, 2007
Guys,
I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.
For example
EmployeeDetail table references Employee table
DepartmentDetail table references Department table
Department table references Employee table
I have 600 tables in my database, out of which 40 tables are look up value tables. I want generate truncate scripts which truncates all the tables in order of Parent child relationship excluding lookup tables. Is there any way to do this apart from figuring out Parent Child relationship and then writing the truncate statements for each of the table.
For example
EmployeeDetail table references Employee table DepartmentDetail table references Department table Department table references Employee table
I've always created database tables with one Primary Key that increments by one for each new record. I'm working on a database that was built by someone else now that has a lot of defined foreign key relationships (under Tables > specific table > Keys). How helpful is it to define these Keys? I assume it helps make the database more efficient but is it necessary if you're working with small databases?
I working a tutorial where I will learn a lot of new stuff, LINQ being one of them. As a part of this tutorial, there are four tables and foreign keys. My db skills were limited to simple tables and i've nevr done anything where I related tables before. I'm a bit confused about foreign keys and how to set them up. I think I'm setting up right, but I have a doubt. I have a couple of screen caps to show you what where my doubt comes from at: http://www.noelakins.com/keys.htmlWhat is causing my confusion is that the connecting lines between the Albums and Events table diagrams in my setup seems to point back to the Photos table, and the Photos table seems to point to the Tags table. From the tutorial, the connecting lines seem to be pointing the other direction. Am I setting up the key right, or do I have them backwards? I'm setting out to learn a lot of stuff in this tutorial, so when I mess it up, i would like to know that it isn't because the foreign keys are wrong.EDIT:After doing some googling and some reading and watching, I'm a little less confused about how to set up foreign keys, at least how to do it in VWD. My problem now seems to be understanding the relationships. In the tutorial example, the Tags table has a FK on the PhotoID field, which matches the primary key (PhotoID) in the Photos table. Now, does this mean that the Tags table is the parent table and the Photos table the child? If this is true, then the other two tables must be child tables of the Photos table. At least that how I think this should work. The issue that I'm having with this is trying to understand how a cascade delete would work. Is the foreign key relationship a one way or both way relationship? For example, if I have made a foreign key on the AlbumID field of the Photos table, where the primary key table is Albums, PK is AlbumID and the foreign key table is Photos, then, if I do a cascade delete of a particular photo, does it delete the album too? That wouldn't make sense. Foreign keys and cascade deletes must be one way, right? Thanks
I'm using SQL Server 2005 Management Studio. I've created several Foreign Key Relationships using Database Diagrams. Now I'm trying to edit the constraints on the relationships, but when I right click on the relationship, the only option available is "Delete Relationships from Database". But I am not able to edit the relationship. Does anyone know how to do this?
I have 2 tables Publication and Advertisment_Codes. I want to update the publication_code in both tables where country_id=1 and publication_code='A'. However, it gives me an error because of foreign key relationship. I tried first updating in Advertisment_Codes and then in Publication table but it didn't work. I tried the other way round but also it didn't work. How can I update the publication code? Thanks
GO ALTER TABLE [dbo].[Advertisment_Codes] WITH CHECK ADD CONSTRAINT [FK_Advertisment_Codes_Countries] FOREIGN KEY([Country_id]) REFERENCES [dbo].[Countries] ([Country_id]) GO ALTER TABLE [dbo].[Advertisment_Codes] CHECK CONSTRAINT [FK_Advertisment_Codes_Countries] GO ALTER TABLE [dbo].[Advertisment_Codes] WITH CHECK ADD CONSTRAINT [FK_Advertisment_Codes_Publications] FOREIGN KEY([Country_id], [Publication_code]) REFERENCES [dbo].[Publications] ([Country_id], [Publication_code]) GO ALTER TABLE [dbo].[Advertisment_Codes] CHECK CONSTRAINT [FK_Advertisment_Codes_Publications]
GO ALTER TABLE [dbo].[Publications] WITH CHECK ADD CONSTRAINT [FK_Publications_Countries] FOREIGN KEY([Country_id]) REFERENCES [dbo].[Countries] ([Country_id]) GO ALTER TABLE [dbo].[Publications] CHECK CONSTRAINT [FK_Publications_Countries]
Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.
Hey guys this is a SQL Server 2005, .net 2.0 question. I got 3 tables: Job, Times, Employee. The times table is related to Job and Employee by foreign keys. The Times table is pretty much a table where the employee's time logs are kept for a specific job. Since there was a M:M relationship between Job - Employee, I decided to create a Times table in between, and the primary key of this table should actually be the PK of Job and the PK Employee, but I couldn't figure out how to make a PK consisting of two fields. Anyhow, my main question is: How to assign an Employee to a specific job in .net? In other words, what's the best way of retrieving the Job_id and Emp_id and sticking them in the Times table? I was doing a form with a drop down for Emp_id and Job_id, but I don't know how to do the inserts... I'm confused and would appreciate any help.THANKS!
How do I move data from one server and append to the same table structure on a second server and preserve the foreign key relationships. In other words TableB.TableA_ID references TableA.ID. The IDENTITY ID numbers themselves will change but they must continue to match up on the destination server the way they did on the source server.
I can do this using procedural programming but that is usually not the way to go.
(btw, this is a cleaned up version of a question I tacked on to another thread)
I cannot find a good way to view existing Foreigh Key Relationships in SQL Server Management Studio Express. In the Object Explorer, if you click on a table to expand it, you can see a tree view of EVERYTHING about the table (Columns, Keys, Contraints, Triggers, Indexes, Statistics), but NOTHING about Foreign Key Relationships!!
The only way I have found is that you have to open the table in Design mode, then right click, and choose Relationships, and only then can you see what FK Relationships are defined.
You should be able to see the FK Rletaionships in the tree view of the Object Explorer window!
Here is the alter statement that I am trying to use to create a relationship between 2 tables. This does not seem to work on mobile. What am I doing wrong?
ALTER TABLE [SubCategory] CONSTRAINT [FK_SubCategory_Category] FOREIGN KEY([CategoryID]) REFERENCES [Category] ([CategoryID]) ON UPDATE CASCADE ON DELETE CASCADE
I received the error below upon saving when I was trying to create a foreign key in a table using relationships in "table designer" of sql server 2005 management studio. This process has been successful for other tables. What might be the problem?
Thanks
Code Snippet
- Unable to create relationship 'FK_SAMPLE_GRID'. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_SAMPLE_GRID". The conflict occurred in database "acquire", table "dbo.GRID", column 'GRIDNAME'.
I have taken on a contract to improve reporting for an old HR database that was developed using FoxPro (Visual FoxPro, I think) with the data stored in SQL Server 2000. There are no foreign keys in SQL Server 2000 so the relationships are maintained inside FoxPro.Is there a way of extracting the relationships from the FoxPro code and generate foreign keys in SQL Server, so that I can do proper design?
I am testing Insert, Update etc. and messing up my database with "dirty" data. Is there a way to temporarily drop FOREIGN KEY constraints to truncate a table after testing without dropping the whole table and rebuilding it? Newbie
Am working on sql database table shifting to new database tables. Am getting the following error while truncating the tables:-
Cannot truncate table 'orderItem' because it is being referenced by a FOREIGN KEY constraint.How can i remove all the items in the table if i found this error.
We used to used Sql Server 2000 DTS to perform daily backups to remote servers very successfully but just cannot do the same with SSIS.
I set my Transfer Sql Transfer Objects Task to use the following settings:
CopyData = true
ObjectsTocopy = All Tables
As soon as it hits a table that has a foreign key, I get the following error:
"Truncate data failed for Table nnn"
I've tried setting DropObjectsFirst = True but this fails too with foreign key violations.
First of all, am I doing something obviously wrong? Is SSIS not smart enough to drop tables in the right order so that we don't get foreign key violations?
Am I totally barking up the wrong tree with this and can anyone suggest a better way of doing remote backups?
I'm going through my tables and rewriting them so that I can create relationship-based constraints and create foreign keys among my tables. I didn't have a problem with a few of the tables but I seem to have come across a slightly confusing hiccup.
Here's the query for my Classes table:
Code:
CREATE TABLE Classes ( class_id INT IDENTITY PRIMARY KEY NOT NULL,
This statement runs without problems and I Create the relationship with my Users table just fine, having renamed it to teacher_id. I have a 1:n relationship between users and tables AND an n:m relationship because a user can be a student or a teacher, the difference is one field, user_type, which denotes what type of user a person is. In any case, the relationship that's 1:n from users to classes is that of the teacher instructing the class. The problem exists when I run my query for the intermediary table between the class and the gradebook:
Code:
CREATE TABLE Classes_have_Grades ( class_id INT PRIMARY KEY NOT NULL,
Query Analyzer spits out: Quote: Originally Posted by Query Analyzer There are no primary or candidate keys in the referenced table 'Classes' that match the referencing column list in the foreign key 'Classes_have_gradesFKIndex2'. Now, I know in SQL Server 2000 you can only have one primary key. Does that mean I can have a multi-columned Primary key (which is in fact what I would like) or does that mean that just one field can be a primary key and that a table can have only the one primary key?
In addition, what is a "candidate" key? Will making the other fields "Candidate" keys solve my problem?
I'm trying to find the most efficient method of importing XML files into SQL Server using SSIS. The objective is to read these files created in a source directory and potentially there will be a large number of these created every second.
I've experimented with the XML Source data flow task and been able to import using either the OLEDB Destination or SQL Server Destination.Using the associated XSD file
These methods work well when there are no PK or FK relationships, i.e. the target tables are stand alone.
However when I use a schema that has relationships I need to be able to import and be able to return the key then use this key to import into another table to ensure the FK relationship and so on.....
From what I know I will need to create multple Data Flow tasks when reading a single xml file then in turn create a 'look up' on a table to see if this entity exists and on error insert using the OLEDB command and then somehow return the PK generated???
Has anyone tried this? And would be happy to share there experiences?
It seems a lot easier to use an SP and then use an execute SQL task method
Here is my issue I am new to 2005 sql server, and am trying to take my old data which is exported to a txt file and import it to tables in sql. The older database is non relational, and I had made several exports for the way I want to build my tables. I built my packages fine and everything is working until I start building relationships. I remove my foreign key and the table with the primary key will get updated for the package again. I need to update the data daily into sql, and once in it will only be update from the package until the database is moved over.
It will run and update with a primary key until I add a foreign key to another database.
Here is my error when running the package when table 2 has a foreign key.
[Execute SQL Task] Error: Executing the query "TRUNCATE TABLE [consumer].[dbo].[Client] " failed with the following error: "Cannot truncate table 'consumer.dbo.Client' because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
hello, i am wondering, for what i use relations between tables? if i make sure that the data inserted into the table matches, for what i need table relationships? i have a table named Pictures with: PictureID, UserID, UserName, PictureName, Description... do i have to make relationships between UserID from Pictures and UserId from aspnet_Users? and the second table PicturesComments witch has: CommentID, PictureID, UserID, UserName... do i have to makre relationship between PictureID from PictureComments and PictureID from Pictures AND between UserID from Pictures and UserId from aspnet_Users? sorry for this stupid questions... thanks
I am creating a small piece of software which handles a pre-advice data file, several scanner inputting weights and a supplier weight report.
The pre-advice contains a list of barcodes and details revolving around that barcode, each barcode will be scanned and a weight will be obtained, this will be place in the DB.
Currently my tables can be summarized like this.
Pre-Advice Table Barcode
Lots of other info…
Scanners Table Barcode Date Time Weight
Which device input this information
Suppliers Weight Table Barcode Date Time Weight
Here is the process(es):
1.Pre-advice table populated.
2.When the barcode is scanned and a weight is obtained, a record will be added to the scanners table and this becomes the ‘active’ weight.If there is already an ‘active’ weight against this barcode the user will be asked if they wish to use the ‘active’ weight already in the system or update the ‘active’ weight to the weight they have entered.
3.Then when the supplier weight report is inputted into its table, should the ‘active’ weight be less than the supplier weight, the supplier weight becomes the ‘active’ weight.
At any given time a user can pull a report based on information in the Pre-Advice table, which will need to include the ‘active’ weight for that barcode’s record.
Additionally, no weights should be deleted, I need a fully traceable log of who, what, where & when. I will need to pull reports on the trials of scans so it really is a must to keep these.
Furthermore, if a scanner was to scan the same barcode after the Suppliers weight had been input and the user chooses to replace the weight then, the scanners weight should become the new ‘active’ weight.
I believe I will need to modify my tables to accommodate my needs, however I am not sure exactly what or how to set up what I need.I understand I will need some type of relationship between all of the tables to be able to pull the report on the Pre-Advice table, but I am lost.
how I will need to set up my tables/relationships in order to achieve my requirements?
There are several databases (currently in Access) that are being moved to SQL Server. Would prefer keeping those databases separate in SQL Server. How do you do something similar to Access's "link" capabilities and relate tables in different physical databases? For example, relate the authors of a document in a document database to persons in a people database where AuthorID in Docs.DocAuthors.AuthorID is related to People.Persons.PersonID
Hi, I have two tables. One is called ContentRecord and it is one field, an identity field that is a primary key. I have another table that is has several fields including a field that is a foreign key called contentFK. I have set up a foreign key relationship between the tables. There is a one to many relationship. I would like to know the best way to add a record to both tables. The way I am thinking to do it is to do the insert statement to add the record to the ContentRecord which will return the value of the primary key field. Then do a seperate insert statement to insert the record into the second table where I set the contentFK equal to the returned value from the first statement. Does that make sense? Is there a better way to do this?
shibu writes "I have a table 'manage_user_types' and its primary key is 'Category_ID'. There is another field 'Category_Parent_ID' which refers the 'Category_ID' in the same table. And one more field is 'Category_Name' which is related to 'Category_ID'. ie.when displaying the table in front end, 'Category_Name' is displayed under 'Category_Parent_ID'. I want to sort the table with 'Category_Name' when clicking the 'Category_Parent_ID' to sort. What should i do?"
Hi, I'm working on Sql Server 2005 Express and I'm trying to transfer over some of my tables with the primary and foreign keys relations as well as the data in them from one asp.net 2.0 website to another.But I can't seem to find the Import/Export option in Sql Server 2005 Express Edition, does anyone know how I can do this?Thanks in advanced.
Can anyone give me an idea like, what percentage of organizations use 'code' to maintain the parent-child relations on their tables than having FK constraints thru the db model? Because,all the companies that I worked with used 'code' to control the relationships across the tables(not the PK/FKs.!!) Thanks. Neil.
I export all tables from serverA databaseAA to serverB (both SQL2k sp3)databaseAA with 'Success', but without any relationships. In SQL6.5 Tools of Transfer will bring all objects include relationship.
I also tried export "All Objects" which fails with unclear message. I just don't want to do a backup and restore....
My SQL is very basic. How do I create a query that will accept a parameter, an integer, and based on the integer, locate all the matches in a db? SELECT COUNT(*) AS Expr1, tblArticle.ArticleIDFROM tblArticle INNER JOIN tblArticleCategory ON tblArticle.ArticleCatID = tblArticleCategory.ACategoryIDGROUP BY tblArticle.ArticleID This isn't setting up the query to request a parameter.What am I doing wrong here? I"m trying to get the total number of articles for a particular category ID.
Hello Friends, I am right now working on a project that has a database with over 100 tables in a database. Because of extreme time constraints the developers didn't build in any relationships or constraints between or in the tables. Now I need to remodel the database such that the database is more structured and normalized. I don't have much knowledge about the database design since it is a 2 year old application and the person who developed the database is now gone. I know remodelling the database would require knowledge of the existing database and business rules. I was wondering if there are any tools that could suggest or discover relationships between tables. For eg. Lets say there are two tables named 'Customer' and 'Order'. I notice that there is a column named 'id' in Customer and a column named 'customer_id' in Order. So I ask the tool to discover a relationship between id and customer_id and it tells me that there is a one-one or one-many or no relationship by comparing values. I heard ERWin would be able to do that but thats expensive. Please do let me know asap.
I've been searching around for some info on how to set this up, but with no luck.I need to have a .sql file that will set up a few tables and these tables will have relationships and contraints.I can do this by hand in enterprise manager, but need to set up some procedures that will do the same thing.For instance, I can create the tables just fine.....CREATE TABLE students ( sId int NOT NULL PRIMARY KEY, studentId varchar(50) NOT NULL, course varchar(50) ) CREATE TABLE courses ( cId int NOT NULL PRIMARY KEY, course varchar(50) NOT NULL, sco varchar(50) )But, I need to set up relationships in there somehow.Once student may have many courses (one to many) and one course may have many sco's (one to many) SCO would be another table.Can someone point me to a good link that would show how to complete these procedures?Thanks all,Zath
