Modifying an Existing Table

[Database Administrator]

The following actually applies to SQLite in my case, but I wasn't sure if
there are some standard SQL statements that do what I need.

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!

--
Jonathan Wood
SoftCircuits Programming
http://www.softcircuits.com

[Database Administrator]

This is a strong reason to strongly name your contraints:

Example:

ALTER TABLE dbo.Employee
ADD CONSTRAINT EMP_State_And_EmpNumber_UNIQUE UNIQUE ( StateID ,
EmployeeNumber )


The crapper is that when you do it "inline", Sql Server willl create a name
for you.

You have to know that (semi random) name to be able to drop it.

You can see this through Management Studio when you drill down into the
Table(s).



"Jonathan Wood" wrote in message
news:ukklcZbRJHA.4524@TK2MSFTNGP03.phx.gbl...
> The following actually applies to SQLite in my case, but I wasn't sure if
> there are some standard SQL statements that do what I need.
>
> 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!
>
> --
> Jonathan Wood
> SoftCircuits Programming
> http://www.softcircuits.com
>

[Database Administrator]

In SQL Server (and most likely in any other product) you can drop UNIQUE
constraint and recreate it.

Since the constraint was created without providing a name, the system
generated the name. You can find that by running the following query
(hopefully it works in your product, as INFORMATION_SCHEMA view are
standard):

SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'UNIQUE'
AND TABLE_NAME = 'Vehicles';

Once you find the name, then on SQL Server you would use ALTER TABLE to
drop the constraint, and then again ALTER TABLE to create the new
constraint (replace with the result from the first query):

ALTER TABLE Vehicles
DROP CONSTRAINT constraint_name;

ALTER TABLE Vehicles
ADD CONSTRAINT un_Vehicles
UNIQUE (FK_CustomerID, VehicleNumber);

--
Plamen Ratchev
http://www.SQLStudio.com

[Database Administrator]

Sorry, I didn't (previously) see the SqlLite reference.

I have no idea about it. Maybe what I said applies, maybe not.




"sloan" wrote in message
news:uwcNghbRJHA.4084@TK2MSFTNGP04.phx.gbl...
> This is a strong reason to strongly name your contraints:
>
> Example:
>
> ALTER TABLE dbo.Employee
> ADD CONSTRAINT EMP_State_And_EmpNumber_UNIQUE UNIQUE ( StateID ,
> EmployeeNumber )
>
>
> The crapper is that when you do it "inline", Sql Server willl create a
> name for you.
>
> You have to know that (semi random) name to be able to drop it.
>
> You can see this through Management Studio when you drill down into the
> Table(s).
>
>
>
> "Jonathan Wood" wrote in message
> news:ukklcZbRJHA.4524@TK2MSFTNGP03.phx.gbl...
>> The following actually applies to SQLite in my case, but I wasn't sure if
>> there are some standard SQL statements that do what I need.
>>
>> 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!
>>
>> --
>> Jonathan Wood
>> SoftCircuits Programming
>> http://www.softcircuits.com
>>
>
>

[Database Administrator]

On Nov 13, 11:36*am, "Jonathan Wood" wrote:
> The following actually applies to SQLite in my case, but I wasn't sure if
> there are some standard SQL statements that do what I need.
>
> 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!
>
> --
> Jonathan Wood
> SoftCircuits Programminghttp://www.softcircuits.com

Also using TEXT for Make/model/VIN is probably an overkill, you can
USE VARCHAR instead. Also why is VehicleYear TEXT instead of INT?

[Database Administrator]

//Also why is VehicleYear TEXT instead of INT?//

Cuz it ain't cool dude. How else is he going to put in

'68 (Ford Mustang)
(notice the apostrophe) ??
(ha ha)





"Alex Kuznetsov" wrote in message
news:79465d53-5296-4e3d-8ba0-4c7a4d225318@b31g2000prb.googlegroups.com...
On Nov 13, 11:36 am, "Jonathan Wood" wrote:
> The following actually applies to SQLite in my case, but I wasn't sure if
> there are some standard SQL statements that do what I need.
>
> 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!
>
> --
> Jonathan Wood
> SoftCircuits Programminghttp://www.softcircuits.com

Also using TEXT for Make/model/VIN is probably an overkill, you can
USE VARCHAR instead. Also why is VehicleYear TEXT instead of INT?

[Database Administrator]

"sloan" wrote in message
news:uwcNghbRJHA.4084@TK2MSFTNGP04.phx.gbl...

> This is a strong reason to strongly name your contraints:

Makes sense.

> You can see this through Management Studio when you drill down into the
> Table(s).

Right. And, as you realized in your other post, this isn't an option here.

Thanks.

Jonathan

[Database Administrator]

"Alex Kuznetsov" wrote in message
news:79465d53-5296-4e3d-8ba0-4c7a4d225318@b31g2000prb.googlegroups.com...

> Also using TEXT for Make/model/VIN is probably an overkill, you can
> USE VARCHAR instead. Also why is VehicleYear TEXT instead of INT?

SQLite is a bit strange. You can store a string in an INT field and store an
INT in a TEXT field. So it really doesn't matter. But I may have used TEXT
to avoid the issue of any validation or conversion as I don't really care
what the user enters there.

Also, the VARCHAR syntax is supported, but it basically is just an alias for
TEXT.

For my particular application, SQL Server is not an option and SQLite is
pretty cool. It actually comes as a source file which I include in my
C++/MFC project. No additional DLLs or connections required!

Jonathan

[Database Administrator]

"Plamen Ratchev" wrote in message
news:BYqdnVKD4a5194HUnZ2dnUVZ_o7inZ2d@speakeasy.ne t...

Thanks. Unfortunately, looks like this is not supported:

"Error compiling query : no such table: INFORMATION_SCHEMA.TABLE_CONSTRAINTS
(error 1)."

Jonathan

> In SQL Server (and most likely in any other product) you can drop UNIQUE
> constraint and recreate it.
>
> Since the constraint was created without providing a name, the system
> generated the name. You can find that by running the following query
> (hopefully it works in your product, as INFORMATION_SCHEMA view are
> standard):
>
> SELECT CONSTRAINT_NAME
> FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> WHERE CONSTRAINT_TYPE = 'UNIQUE'
> AND TABLE_NAME = 'Vehicles';
>
> Once you find the name, then on SQL Server you would use ALTER TABLE to
> drop the constraint, and then again ALTER TABLE to create the new
> constraint (replace with the result from the first
> query):
>
> ALTER TABLE Vehicles
> DROP CONSTRAINT constraint_name;
>
> ALTER TABLE Vehicles
> ADD CONSTRAINT un_Vehicles
> UNIQUE (FK_CustomerID, VehicleNumber);
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com

[Database Administrator]

I have turned to strongly naming all my
CONSTRAINTS
ForeignKeys
PrimaryKeys
Indexes

pretty much everything...because its easier to track down later.

The "semi random name" thing just slows you up later.

Pay a price up front, save time later is my motto here.


I know you're not on Sql Server, but seeing those "real" names vs semi
random names is much nicer when the poop is hitting the ceiling.



"Jonathan Wood" wrote in message
news:OB9GhFcRJHA.5080@TK2MSFTNGP03.phx.gbl...
> "sloan" wrote in message
> news:uwcNghbRJHA.4084@TK2MSFTNGP04.phx.gbl...
>
>> This is a strong reason to strongly name your contraints:
>
> Makes sense.
>
>> You can see this through Management Studio when you drill down into the
>> Table(s).
>
> Right. And, as you realized in your other post, this isn't an option here.
>
> Thanks.
>
> Jonathan
>