MS-SQL Foreign Key - Re-create and Remove
Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums
Forum Description: Support forum for Web Wiz Forums application.
URL: https://forums.webwiz.net/forum_posts.asp?TID=31495
Printed Date: 28 March 2026 at 9:09pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com
Topic: MS-SQL Foreign Key - Re-create and Remove
Posted By: negrisoli
Subject: MS-SQL Foreign Key - Re-create and Remove
Date Posted: 01 August 2017 at 12:54am
|
Hello all, I would like to know if can I remove and re-create the Foreign Key in MS-SQL database ? I need to manipulate some datas and I can't because of the Keys. Please, tell me If someone had the same needs. Tks. |
Replies:
Posted By: WebWiz-Bruce
Date Posted: 01 August 2017 at 7:30am
|
You have not mentioned which Foreign Key in the Web Wiz Forums database. However, the Foreign Keys are there to keep the tables linked, removing the Foreign Key will likely cause problems within Web Wiz Forums. ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: negrisoli
Date Posted: 01 August 2017 at 5:46pm
|
All the keys ! Unfortunately, I saw that my database have some datas and columns that are old and not exist anymore inside the new database. I've been compared a new structure with my version , and have some differents properties , like: Smallint , nText , Nvarchar , etc ... that were changed for another or don't have anymore in new one. Maybe it's not a danger , but I'd like to be update with the new version. I don't have much experience with database and I'm looking for a script that could have update it, remove and re-create the Keys , beucause today I can't to change/modify one column or datas because the Keys. Then, if you have some script, it will be helpful for me. Thanks a lot. |
Posted By: WebWiz-Bruce
Date Posted: 02 August 2017 at 12:03pm
|
Some data types are interchangeable such as Smallint and Bit, nText and Text, Nvarchar and Varchar so you may not need to change these. The data in primary and foreign keys is controlled by the database and these fields are linked between tables should NEVER be manually changed. Doing so will likely break the database and cause your forum to not work correctly. ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: negrisoli
Date Posted: 02 August 2017 at 3:43pm
|
Perfect !!! Good to know it !!! Now, I stay more calm :-) Thanks a lot.
|
Posted By: negrisoli
Date Posted: 05 August 2017 at 10:04pm
@Bruce, so sorry, but I'm a little bit stubborn and I've researched more about the use and functionality of Database MS-SQL  @For all ... Well, it is possible to adjust the properties of the tables, like Primary Key (PK), Foreign Key (FK), remove and create new fields and structures and so on. So if anyone has or has had the same problem as me, here's a little tip. First, you must have to take a backup of your Database and make sure it is not being used by the Forum or another User connected in your DB, it is important that the datas are corrects and your bank is functioning normally too. Using SQL Management Studio, you can remove all PK and FK keys, and after that you will can work on all tables without problems, correct fields, properties, or even Drop or Delete fields or tables, but I suggest not removing any data or modify the default structure of the Database, this tip is just to help you tweak and fix your structure in case of upgrade from old version like me. Once you have made the adjustments, you can recreate all the primary keys (PK) again and once all the keys are recreated, you can execute the script below to recreate all foreign keys (FK) and Constraint keys again. REMOVED BY ADMIN I'm not an expert in DB , but I fixed my issues and I think these tips will can help someone ! Hugs.
|
Posted By: IanSmithISA
Date Posted: 06 August 2017 at 5:43am
|
Good morning, This really is a very bad idea
 If you are an experienced programmer then the forum is quite easy to understand overall but even then I would expect there to be a gotcha or two, You say I don't have much experience with database and I'm looking for a script that could have update it, remove and re-create the Keys , beucause today I can't to change/modify one column or datas because the Keys. This means that there is data in one table that the forum expects to be linked to another table and you want to change it in a way that will break these expectations. If you need to remove a foreign key to change data and then reinstate it afterwards then you are almost certain to have broken something!  If you are lucky it will be immediately obvious and you can just restore your backup, otherwise problems may take a while to appear. For example tblThread (each post such as this message) is referenced by tblTopic (each topic such as MS-SQL Foreign Key - Re-create and Remove) to know the first and last post What will happen if you manually delete this post without updating those references can only be determined by looking at all of the code that references those columns. For example the Latest Forum Posts part of the home page could start showing empty entries depending upon how the code was written. Kind regards Ian ------------- Why does anyone need more than an 80x25 character only VDU? |
Posted By: negrisoli
Date Posted: 06 August 2017 at 5:50pm
|
Hello Ian, How are you ? Yes, I don't have much experience with DB, but my DB is very very old, there are a lot of fields with differents properties and fields that they not existing anymore according in the new DB. I think that my DB was from 2007 , it is very old ! The idea was update these fields and proporties, and I had some difficult because of the PK and FK. Then, I researched a lot, for some months, and I learned a little bit more about DB and in fact, it is possible to do, but as you said, must have take care and I recommend to take a backup before to fix it or work in your DB. See the links that I used. I've made a lot of backups, because I have many datas, users, foruns, topics, private messages, polls, etc. It were many tests before to go in production DB, but I got to fix, I updated ALL tables, including the tblThread, I removed the old fields, but before I certified that didn't have any datas there, I altered the properties, like NVARCHAR, Smallint, nText, sizes and lenght, etc .... well , it was a success, now my DB is with ALL corrections and the PK and FK again !!  As Bruce told in other post, some properties are similar and don't take any problem, but I would like to be with all corrected. Maybe, there are others ways easier to fix it , but as I said, I don't have much experience and these articles that I found were the enough to fix my issue. If you know how, could you share with us. If someone has with the same issue, will can be try using this tips, but don't forget to take a backup before, because if don't have a success it is only to restore the DB again. Thank you for your concern. Hugs.
|
Posted By: WebWiz-Bruce
Date Posted: 07 August 2017 at 8:47am
|
Why would you want to delete and recreate the primary and foreign keys? As a DB Admin with over 20 years experience with SQL Server this is a VERY BAD idea and should NEVER be done. I have removed the SQL that you posted to do this as it is extremely important that no-one does this as you will almost certainly end up with a broken Web Wiz Forums database that will give you stability and errors within your forum. ------------- https://www.webwiz.net/web-wiz-forums/forum-hosting.htm" rel="nofollow - Web Wiz Forums Hosting https://www.webwiz.net/web-hosting/windows-web-hosting.htm" rel="nofollow - ASP.NET Web Hosting |
Posted By: negrisoli
Date Posted: 07 August 2017 at 3:50pm
|
Bruce, As a told you, my DB was very old and there were many differents fields, with different properties, like type, size, length, etc. and I needed to update them. As you said me, these were not necessary, but I would like to have a clean DB and with the actual version. Well, I'm very and extremely happy, because I did and it was a success ! Therefore, I've just shared this for help anothers. It was only a post for share an idea, but if you don't think so, it is fine. Sorry for that and thanks a lot for your concern. Regards.
|