Primary Key & Foreign Key Constraints

USE Rohan

GO

CREATE TABLE FILES

(

FILEID INT CONSTRAINT PK_FLID PRIMARY KEY,

FILENAME VARCHAR (10)

);

GO

CREATE TABLE SUPPORTINGFILES

(

SUPPORTID INT CONSTRAINT PK_SID PRIMARY KEY,

FILEID INT CONSTRAINT FK_SUPPORTFILES_FID FOREIGN KEY REFERENCES FILES(FILEID)

);

GO

·         When we are creating a table, we have to be pretty clear that which column needs to be a primary key.

·         If we are creating the primary key and that was referenced by another column as a foreign key

in another table , then we cannot drop the table which has a primary key. See below

             “Msg 3726, Level 16, State 1, Line 2

  Could not drop object 'FILES' because it is referenced by a FOREIGN KEY constraint.”

·         If you want to drop the table(FILES table) which has a primary key the first thing you have to do is drop the foreign key constraint(CONSTRAINT FK_SUPPORTFILES_FID in SUPPORTINGFILES table)

ALTER TABLE SUPPORTINGFILES

DROP CONSTRAINT FK_SUPPORTFILES_FID

ALTER TABLE SUPPORTINGFILES

DROP COLUMN FILEID

·         If you want to drop the foreign key column(FILEID in SUPPORTINGFILES table) first you have to drop the constraint and then you can drop the column

ALTER TABLE SUPPORTINGFILES

DROP CONSTRAINT FK_SUPPORTFILES_FID