0

I am trying to change a column type:

This is the code I have made so far, but it doesn't work at all.

ALTER TABLE VILLE
ALTER COLUMN NVIL char(12);   <-- Here I am trying to change from char(4) to char(12),
ALTER TABLE CLIENT                I do this to add a foreign key to it
ADD FOREIGN KEY (LOCALITE)    
REFERENCES VILLE

Can someone help me to modify my query?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3742475
  • 7
  • 1
  • 2
  • 6
  • `express is a minimal and flexible Node.js web application framework`. Are you sure you meant to use that tag? What RDBMS are you using? – Barmar Oct 22 '14 at 15:56
  • Yes, it's Sql Server Express I'm using, but my teacher told me we could learn these type of queries with express – user3742475 Oct 22 '14 at 15:58
  • NVIL needs to be a primary key or have a unique index/constraint in order to point a foreign key at it (and the foreign key really should specify which column(s) it is pointing to, not just the table name). Also if there is already a PK on this column, you need to drop it in order to change the column data type (even though it's not possible for such a change to violate the constraint), and then re-create it. – Aaron Bertrand Oct 22 '14 at 16:06
  • Okay, so there is no possibility to modify a column without dropping it? – user3742475 Oct 22 '14 at 16:16
  • You need to drop the primary key constraint, not the column. Then you can alter the column. Then you can re-create the primary key constraint. Then you can create the foreign key constraint (but please do it right, by specifying the column too). – Aaron Bertrand Oct 22 '14 at 16:18
  • I tried this code but it still doesn't work, (from what I understood. The primary key should be dropped like this: ALTER TABLE Table1 DROP CONSTRAINT PK_Table1_Col1) ALTER TABLE VILLE DROP CONSTRAINT PK_VILLE_NVIL; ALTER TABLE VILLE ALTER COLUMN NVIL char(12); ALTER TABLE CLIENT ADD FOREIGN KEY (LOCALITE) REFERENCES VILLE (NVIL); – user3742475 Oct 22 '14 at 16:57

2 Answers2

0

You are missing "(NVIL)" from the end of your script to add a foreign key...?

Your two queries should be:

ALTER TABLE VILLE ALTER COLUMN NVIL CHAR(12);
ALTER TABLE CLIENT ADD FOREIGN KEY (LOCALITE) REFERENCES VILLE (NVIL);

But before running this you will need to run:

ALTER TABLE VILLE DROP CONSTRAINT PK__VILLE__7EF717197DFD96C8;

On completion you can add your primary key back again, but you might want to give it a better name than the one SQL Express picked for you?

ALTER TABLE VILLE ADD CONSTRAINT pk_VILLE PRIMARY KEY (NVIL);
Richard Hansell
  • 5,315
  • 1
  • 16
  • 35
  • I tried to modify it but I receive this message: Msg 5074, Level 16, State 1, Line 1 The object 'PK__VILLE__7EF717197DFD96C8' is dependent on column 'NVIL'. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN NVIL failed because one or more objects access this column. – user3742475 Oct 22 '14 at 16:04
  • Yes, you will need to drop the primary key constraint, modify the column then put the primary key constraint back in place to be able to use it as a foreign key constraint... basically what Aaron says above. – Richard Hansell Oct 22 '14 at 16:35
0

The type and definition of foreign key field and reference must be equal. This means your foreign key disallows changing the type of your field.

One solution would be this:

LOCK TABLES 
    Table 1 WRITE, -- pk table
    Table 2 WRITE; -- fk table

ALTER TABLE Table 2
    DROP FOREIGN KEY 'foreign name',
    MODIFY NVIL char(12);

recreate foreign key

ALTER TABLE Table 2
    ADD FOREIGN KEY CONSTRAINT 'foreign name' FOREIGN KEY (Table1.NVIL)

UNLOCK TABLES;