Syntax Help please - how to drop a foreign key

Question

Syntax Help please - how to drop a foreign key

mw_sql_developer

SSCoach

Points: 19445
More actions
December 23, 2015 at 11:50 am

#323224

The following is not right.. If you can help me please .....
ALTER TABLE ENROLLMENT_IN
DROP
FOREIGN KEY ([FILE_ID]) REFERENCES FILE_ID_IN([FILE_ID])

Viewing 15 posts - 1 through 14 (of 14 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

mw112009 (12/23/2015)
The following is not right.. If you can help me please .....
ALTER TABLE ENROLLMENT_IN
DROP
FOREIGN KEY ([FILE_ID]) REFERENCES FILE_ID_IN([FILE_ID])

Try this:

ALTER TABLE ENROLLMENT_IN

DROP

CONSTRAINT <fk_constraint_name>;

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

mw_sql_developer

SSCoach

Points: 19445

December 23, 2015 at 12:15 pm

#1847829

Tell me how to get the value of <fk_constraint_name>

Lowell SSC Guru Points: 323518 More actions · Answer 3

sp_help ENROLLMENT_IN is one way, you can scroll down to the foreign leys.

another way, you can use the metadata to find out the information;

select * from sys.foreign_keys WHERE object_id = Object_id('dbo.ENROLLMENT_IN')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

johnwalker10 SSCrazy Eights Points: 9124 More actions · Answer 4

ALTER TABLE [dbo].[ table_name] DROP CONSTRAINT [fk_name]

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 5

Ok... if have to ask... why was the following post marked as the correct answer...

johnwalker10 (12/23/2015)
ALTER TABLE [dbo].[ table_name] DROP CONSTRAINT [fk_name]

... even though it has less information that this post?

Lynn Pettis (12/23/2015)
mw112009 (12/23/2015)
The following is not right.. If you can help me please .....
ALTER TABLE ENROLLMENT_IN
DROP
FOREIGN KEY ([FILE_ID]) REFERENCES FILE_ID_IN([FILE_ID])
Try this:
ALTER TABLE ENROLLMENT_IN
DROP
CONSTRAINT <fk_constraint_name>;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

gilbertashley Newbie Points: 3 More actions · Answer 6

Utilize asleep time for you to replenish the water inside your arms and toes Skin Care Tips through the use of moisture and sleeping with clothes on. It is possible to use clothes on your own palms along with your feet but be sure they're 100% cotton. Hanes cotton stockings are preferred by me but model doesn't really matter. Curel makes a lotion that works well on your legs and hands but I would like to tell you about a key item I recently available at Goal.

Place all the herbs in a broad mouthed bottle and address with all the witch hazel extract. Cover let remain for 2-3 months and closely. Currently, you will stress the mix, and add one half pot of the rosewater. You may even at this time add some gas including our decision, rose, or lavender. Place mixture in a jar that is pretty. This tonic does not must be chilled.

Read More Info ====>>> http://svelmeblog.com/dermafy-serum/

kaza_rohan SSC-Addicted Points: 435 More actions · Answer 7

ALTER TABLE tablename

DROP CONSTRAINT constraintname

ALTER TABLE tablename

DROP COLUMN column name

FROM : ROHAN K

UseFul Site:

http://msbitech-buddy.blogspot.in/2015/12/primary-key-foreign-key-constraints.html

mw_sql_developer SSCoach Points: 19445 More actions · Answer 8

Agree!

Actually Lowell and Lynn get the credit!

Why ? Lynn provided the final statement that I needed, but then forgot to tell me how I would get the name of the constraint. Then Lowell replied back with the missing piece.

At the same time J WALKER had given the same reply ( same as Lynn )

So then I was only able to mark one of them as the final solution.

Hey, no hard feelings....

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

mw112009 (12/28/2015)
Agree!
Actually Lowell and Lynn get the credit!
Why ? Lynn provided the final statement that I needed, but then forgot to tell me how I would get the name of the constraint. Then Lowell replied back with the missing piece.
At the same time J WALKER had given the same reply ( same as Lynn )
So then I was only able to mark one of them as the final solution.
Hey, no hard feelings....

I forgot nothing. You asked for the syntax to drop a foreign key constraint, I provided that for you. I thought you already knew the name of the constraint, which you could also find using the object explorer in SSMS.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 10

Agree

You didn't forget..

However I needed it and you probably assumed I knew it.

Either way, I did not know how to get it

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 11

mw112009 (12/28/2015)
Agree
You didn't forget..
However I needed it and you probably assumed I knew it.
Either way, I did not know how to get it

As I said, all you asked for was the syntax to drop a foreign key constraint and that is what I provided. Sorry if the crystal ball was broken and I didn't realize you also needed to know how to get the name of the constraint. Perhaps if you had included that up front you would have gotten a more complete answer.

Of course, from looking at the thread it appears you didn't know you needed that information until after you had the syntax for dropping it.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 12

Pardon my ignorance.

I didn't even know there was a sys.foreign_keys in existence.

So how would I ask you that ?

I didn't think you would be needing a constraint name..

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

mw112009 (12/28/2015)
Pardon my ignorance.
I didn't even know there was a sys.foreign_keys in existence.
So how would I ask you that ?
I didn't think you would be needing a constraint name..

Want an easy way?

In Object Explorer expand the Database folder so you can see the databases. Then expand the database folder you are working with to see the Database Diagram, Tables, Views, etc. folders. Expand the Tables folder to see all the tables. Expand the table folder you are working with to see the Columns, Keys, etc. folders. Expand the Keys folder and you will see the primary key and all foreign keys defined on the table. Right clink the foreign key in question and make the appropriate selection, usually script key as. You can then create a script to create, drop, or drop and create.

When you script to a query window you will get the code needed to accomplish the appropriate task; create, drop, drop and create. It is a good way to learn the syntax needed for each task.

mw_sql_developer SSCoach Points: 19445 More actions · Answer 14

God Blesses those who bless others..

Amen and Thank You