Re: [sqlite] How to Modify Table

[jonwood]

Slater, Chad wrote:
> 
> Sqlite does not support modifying the unique constraints on a table:
> 
> http://www.sqlite.org/lang_altertable.html
> 
> So you probably need to do it the 'ol fashioned way:
> 

Perfect--Just what I figured I'd need to do!

Thanks!
-- 
View this message in context: 
http://www.nabble.com/How-to-Modify-Table-tp20474500p20487732.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How to Modify Table

[Slater, Chad]

Sqlite does not support modifying the unique constraints on a table:

http://www.sqlite.org/lang_altertable.html

So you probably need to do it the 'ol fashioned way:

BEGIN;

CREATE TABLE Vehicles_new (
  VehicleID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  FK_CustomerID INTEGER NOT NULL,
  VehicleNumber INTEGER NOT NULL,
  VehicleTitle TEXT,
  VehicleMake TEXT,
  VehicleModel TEXT,
  VehicleYear TEXT,
  VehicleVIN TEXT,
  VehicleDescription TEXT,
  Active INTEGER DEFAULT 1 NOT NULL,
  CreateDate DATE DEFAULT (date('now','localtime')) NOT NULL,
  UNIQUE (FK_CustomerID, VehicleID));

INSERT INTO Vehicles_new
   SELECT * FROM Vehicles;

DROP TABLE Vehicles;

ALTER TABLE Vehicles_new RENAME TO Vehicles;

COMMIT TRANSACTION;


-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of jonwood
Sent: Wednesday, November 12, 2008 8:08 PM
To: sqlite-users@sqlite.org
Subject: [sqlite] How to Modify Table


My database contains the following table:

m_Database.ExecNonQuery(_T("CREATE TABLE Vehicles (")
  _T("VehicleID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,")
  _T("FK_CustomerID INTEGER NOT NULL,")
  _T("VehicleNumber INTEGER NOT NULL,")
  _T("VehicleTitle TEXT,")
  _T("VehicleMake TEXT,")
  _T("VehicleModel TEXT,")
  _T("VehicleYear TEXT,")
  _T("VehicleVIN TEXT,")
  _T("VehicleDescription TEXT,")
  _T("Active INTEGER DEFAULT 1 NOT NULL,")
  _T("CreateDate DATE DEFAULT (date('now','localtime')) NOT NULL,")
  _T("UNIQUE (FK_CustomerID, VehicleID))"));

I would like to change the last line to instead be:

  _T("UNIQUE (FK_CustomerID, VehicleNumber))"));

Is there any way to make this change to the existing table without
losing
data in the table?

Thanks for any suggestions!
-- 
View this message in context:
http://www.nabble.com/How-to-Modify-Table-tp20474500p20474500.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] How to Modify Table

[jonwood]

My database contains the following table:

m_Database.ExecNonQuery(_T("CREATE TABLE Vehicles (")
  _T("VehicleID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,")
  _T("FK_CustomerID INTEGER NOT NULL,")
  _T("VehicleNumber INTEGER NOT NULL,")
  _T("VehicleTitle TEXT,")
  _T("VehicleMake TEXT,")
  _T("VehicleModel TEXT,")
  _T("VehicleYear TEXT,")
  _T("VehicleVIN TEXT,")
  _T("VehicleDescription TEXT,")
  _T("Active INTEGER DEFAULT 1 NOT NULL,")
  _T("CreateDate DATE DEFAULT (date('now','localtime')) NOT NULL,")
  _T("UNIQUE (FK_CustomerID, VehicleID))"));

I would like to change the last line to instead be:

  _T("UNIQUE (FK_CustomerID, VehicleNumber))"));

Is there any way to make this change to the existing table without losing
data in the table?

Thanks for any suggestions!
-- 
View this message in context: 
http://www.nabble.com/How-to-Modify-Table-tp20474500p20474500.html
Sent from the SQLite mailing list archive at Nabble.com.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users