CODEWITHSUNDEEP
sqlsql-server-express
user3742475
user3742475

Reputation: 5

SQL - Cannot change column type

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?

Upvotes: 0

Views: 3691

Answers (2)

user1364100
user1364100

Reputation:

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;

Upvotes: 0

Richard Hansell
Richard Hansell

Reputation: 5403

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);

Upvotes: 0

Related Questions