column length

Question

column length

Janice R

SSC Enthusiast

Points: 125
More actions
July 22, 2005 at 6:08 am

#88302

If I go into EM and change the length of a varchar column from say 30 to 32 in one table, what effect might that have on the rest of the DB? Looks like this is something that can be done on the fly but I'm not sure??

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

IF you have foreign keys looking at that column you won't be able to do this. You will need to remove them, change the column(s), re-add the FKs. Other than that should not affect any other table.

However, this may affect transactions currently in your system as the table alter needs to complete prior to access to the table is re-granted (by the system automatically).

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Janice R SSC Enthusiast Points: 125 More actions · Answer 2

Thank you .. that is 'kinda' what I thought but I'm still learning this stuff. The REASON I wanted to change the column length was because of an error from another program trying to insert data into this particular table. here's part of the error:

INSERT statement conflicted with TABLE FOREIGN KEY constraint 'FK_Schedule_Reports'.

I guess my question REALLY is.. how do I interogate or document or query this table foreign key constraint and find out EXACTLY what it wants or doesn't want

so that I can go back and look at the record that the import job is puking on and find out how to change that record to conform to the foreign key constraint??????

DCPeterson SSCoach Points: 15266 More actions · Answer 3

There are several ways to see what the definition of a foreign key is, but my preferred method is to open up Query Analyzer, in the Object Explorer pane expand the database and the table you are interested in, then expand the Constraints now you should be able to see the FK in question and can right click it and script it to the clipboard or to a new window. Now you can see exactly where it references.

You can also create a database diagram (if one doesn't already exist) and then open it up and double click on the relationship in question.

Or you can use EM to right click on the table, choose Design Table and then select Manage Relationships...

/*****************

If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek

*****************/