Re: [sqlite] ADD UNIQUE CONSTRAINT

[P Kishor]

On Sun, Oct 10, 2010 at 3:08 AM, Fadhel Al-Hashim  wrote:
> Thank you,
>
> I was wondering about adding a new constraint to an existing column that is
> currently holding data.

As you can see from the syntax diagram, you can't just add a
constraint. You have to add a column. You can always update the new
constraint-full column with the value from the old constraint-less
column.

Or, recreate the table and copy data from the old table.


>
> On Sun, Oct 10, 2010 at 10:54 AM, P Kishor  wrote:
>
>> On Sun, Oct 10, 2010 at 2:45 AM, Fadhel Al-Hashim 
>> wrote:
>> > Good day,
>> >
>> > is it possible to Alter a table and add a unique constraint on one or
>> more
>> > columns?
>> >
>>
>> See http://www.sqlite.org/lang_altertable.html
>>
>> In particular --
>>
>> "The ADD COLUMN syntax is used to add a new column to an existing
>> table. The new column is always appended to the end of the list of
>> existing columns. The column-def rule defines the characteristics of
>> the new column. The new column may take any of the forms permissible
>> in a CREATE TABLE statement, with the following restrictions:
>>
>> The column may not have a PRIMARY KEY or UNIQUE constraint.
>> The column may not have a default value of CURRENT_TIME, CURRENT_DATE,
>> CURRENT_TIMESTAMP, or an expression in parentheses.
>> If a NOT NULL constraint is specified, then the column must have a
>> default value other than NULL.
>> If foreign key constraints are enabled and a column with a REFERENCES
>> clause is added, the column must have a default value of NULL.
>> Note also that when adding a CHECK constraint, the CHECK constraint is
>> not tested against preexisting rows of the table. This can result in a
>> table that contains data that is in violation of the CHECK constraint.
>> Future versions of SQLite might change to validate CHECK constraints
>> as they are added."
>>
>> > thanks,
>> >
>> > fadhel
>> > ___
>> > sqlite-users mailing list
>> > sqlite-users@sqlite.org
>> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>> >
>>
>>
>>
>> --
>> Puneet Kishor http://www.punkish.org
>> Carbon Model http://carbonmodel.org
>> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
>> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
>> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
>> ---
>> Assertions are politics; backing up assertions with evidence is science
>> ===
>> ___
>> 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
>



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] ADD UNIQUE CONSTRAINT

[Fadhel Al-Hashim]

Thank you,

I was wondering about adding a new constraint to an existing column that is
currently holding data.

On Sun, Oct 10, 2010 at 10:54 AM, P Kishor  wrote:

> On Sun, Oct 10, 2010 at 2:45 AM, Fadhel Al-Hashim 
> wrote:
> > Good day,
> >
> > is it possible to Alter a table and add a unique constraint on one or
> more
> > columns?
> >
>
> See http://www.sqlite.org/lang_altertable.html
>
> In particular --
>
> "The ADD COLUMN syntax is used to add a new column to an existing
> table. The new column is always appended to the end of the list of
> existing columns. The column-def rule defines the characteristics of
> the new column. The new column may take any of the forms permissible
> in a CREATE TABLE statement, with the following restrictions:
>
> The column may not have a PRIMARY KEY or UNIQUE constraint.
> The column may not have a default value of CURRENT_TIME, CURRENT_DATE,
> CURRENT_TIMESTAMP, or an expression in parentheses.
> If a NOT NULL constraint is specified, then the column must have a
> default value other than NULL.
> If foreign key constraints are enabled and a column with a REFERENCES
> clause is added, the column must have a default value of NULL.
> Note also that when adding a CHECK constraint, the CHECK constraint is
> not tested against preexisting rows of the table. This can result in a
> table that contains data that is in violation of the CHECK constraint.
> Future versions of SQLite might change to validate CHECK constraints
> as they are added."
>
> > thanks,
> >
> > fadhel
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> Puneet Kishor http://www.punkish.org
> Carbon Model http://carbonmodel.org
> Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
> Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
> Nelson Institute, UW-Madison http://www.nelson.wisc.edu
> ---
> Assertions are politics; backing up assertions with evidence is science
> ===
> ___
> 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

Re: [sqlite] ADD UNIQUE CONSTRAINT

[P Kishor]

On Sun, Oct 10, 2010 at 2:45 AM, Fadhel Al-Hashim  wrote:
> Good day,
>
> is it possible to Alter a table and add a unique constraint on one or more
> columns?
>

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

In particular --

"The ADD COLUMN syntax is used to add a new column to an existing
table. The new column is always appended to the end of the list of
existing columns. The column-def rule defines the characteristics of
the new column. The new column may take any of the forms permissible
in a CREATE TABLE statement, with the following restrictions:

The column may not have a PRIMARY KEY or UNIQUE constraint.
The column may not have a default value of CURRENT_TIME, CURRENT_DATE,
CURRENT_TIMESTAMP, or an expression in parentheses.
If a NOT NULL constraint is specified, then the column must have a
default value other than NULL.
If foreign key constraints are enabled and a column with a REFERENCES
clause is added, the column must have a default value of NULL.
Note also that when adding a CHECK constraint, the CHECK constraint is
not tested against preexisting rows of the table. This can result in a
table that contains data that is in violation of the CHECK constraint.
Future versions of SQLite might change to validate CHECK constraints
as they are added."

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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] ADD UNIQUE CONSTRAINT

[Fadhel Al-Hashim]

Good day,

is it possible to Alter a table and add a unique constraint on one or more
columns?

thanks,

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