Web Wiz - Solar Powered Eco Web Hosting

  New Posts New Posts RSS Feed - MS-SQL Foreign Key - Re-create and Remove
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

MS-SQL Foreign Key - Re-create and Remove

 Post Reply Post Reply Page  12>
Author
negrisoli View Drop Down
Groupie
Groupie


Joined: 13 April 2006
Location: Brazil
Status: Offline
Points: 79
Post Options Post Options   Thanks (0) Thanks(0)   Quote negrisoli Quote  Post ReplyReply Direct Link To This Post Topic: MS-SQL Foreign Key - Re-create and Remove
    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.



Edited by negrisoli - 01 August 2017 at 1:04am
Back to Top
WebWiz-Bruce View Drop Down
Admin Group
Admin Group
Avatar
Web Wiz Developer

Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9791
Post Options Post Options   Thanks (0) Thanks(0)   Quote WebWiz-Bruce Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
negrisoli View Drop Down
Groupie
Groupie


Joined: 13 April 2006
Location: Brazil
Status: Offline
Points: 79
Post Options Post Options   Thanks (0) Thanks(0)   Quote negrisoli Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
WebWiz-Bruce View Drop Down
Admin Group
Admin Group
Avatar
Web Wiz Developer

Joined: 03 September 2001
Location: Bournemouth
Status: Offline
Points: 9791
Post Options Post Options   Thanks (0) Thanks(0)   Quote WebWiz-Bruce Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
negrisoli View Drop Down
Groupie
Groupie


Joined: 13 April 2006
Location: Brazil
Status: Offline
Points: 79
Post Options Post Options   Thanks (0) Thanks(0)   Quote negrisoli Quote  Post ReplyReply Direct Link To This Post Posted: 02 August 2017 at 3:43pm
Perfect !!!

Good to know it !!!

Now, I stay more calm :-)

Thanks a lot.
Back to Top
negrisoli View Drop Down
Groupie
Groupie


Joined: 13 April 2006
Location: Brazil
Status: Offline
Points: 79
Post Options Post Options   Thanks (0) Thanks(0)   Quote negrisoli Quote  Post ReplyReply Direct Link To This Post 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 Tongue

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


Edited by WebWiz-Bruce - 07 August 2017 at 8:46am
Back to Top
IanSmithISA View Drop Down
Groupie
Groupie


Joined: 21 October 2010
Location: Worcester
Status: Offline
Points: 127
Post Options Post Options   Thanks (0) Thanks(0)   Quote IanSmithISA Quote  Post ReplyReply Direct Link To This Post Posted: 06 August 2017 at 5:43am
Good morning,

This really is a very bad idea Smile

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! Unhappy

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


Edited by IanSmithISA - 06 August 2017 at 7:13am
Why does anyone need more than an 80x25 character only VDU?
Back to Top
negrisoli View Drop Down
Groupie
Groupie


Joined: 13 April 2006
Location: Brazil
Status: Offline
Points: 79
Post Options Post Options   Thanks (0) Thanks(0)   Quote negrisoli Quote  Post ReplyReply Direct Link To This Post 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 !! SmileWink

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.
Back to Top
 Post Reply Post Reply Page  12>

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.07
Copyright ©2001-2024 Web Wiz Ltd.


Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Policy

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2024 Web Wiz Ltd. All rights reserved.