Обсуждение: CREATE TABLE AS ...

Hi,

In a data model change, I wanted to use the "create table as select"
feature. However, I want that table to have a primary key defined, and
thus a field defined as NOT NULL. I didn't find a way to add a NOT NULL
constraint on a column of an existing table. If there isn't, what's the
best way to go? A COPY?

Thanks.

Raph

On Tue, Jul 23, 2002 at 03:48:07PM +0200, Raphael Bauduin wrote:
> Hi,

    Hello,

> In a data model change, I wanted to use the "create table as select"
> feature. However, I want that table to have a primary key defined, and
> thus a field defined as NOT NULL. I didn't find a way to add a NOT NULL
> constraint on a column of an existing table. If there isn't, what's the
> best way to go? A COPY?

    Yes, it looks that you cannot add NOT NULL constraint. But check if
this could work for you:

== 8< ===
ALTER TABLE your_table ADD CONSTRAINT my_constr1 CHECK (your_column IS NOT NULL UNIQUE);
ALTER TABLE your_table ADD CONSTRAINT my_constr2 UNIQUE (your_column);
== 8< ===

    It is not marked as "Primary Key", but it does the same job: checks
that column is UNIQUE and NOT NULL.

    Best regards,
--
--- Artur Pietruk, arturp@plukwa.net

On Tue, Jul 23, 2002 at 15:48:07 +0200,
  Raphael Bauduin <raphael@be.easynet.net> wrote:
> Hi,
>
> In a data model change, I wanted to use the "create table as select"
> feature. However, I want that table to have a primary key defined, and
> thus a field defined as NOT NULL. I didn't find a way to add a NOT NULL
> constraint on a column of an existing table. If there isn't, what's the
> best way to go? A COPY?

Another option is to use create table and insert select. The create can
then set up the table exactly like you need. And then you can copy over
data using insert selecting from the source table.

Hi,

Thanks for the tips, but although the result is the same as a primary
key constraint, I'm not sure it will fit my needs as I want to use the
primary key in a foreign key constraint in another table. I made it with
a copy and it works fine.

Raph

On Tue, Jul 23, 2002 at 04:40:32PM +0200, Artur Pietruk wrote:
> On Tue, Jul 23, 2002 at 03:48:07PM +0200, Raphael Bauduin wrote:
> > Hi,
>
>     Hello,
>
> > In a data model change, I wanted to use the "create table as select"
> > feature. However, I want that table to have a primary key defined, and
> > thus a field defined as NOT NULL. I didn't find a way to add a NOT NULL
> > constraint on a column of an existing table. If there isn't, what's the
> > best way to go? A COPY?
>
>     Yes, it looks that you cannot add NOT NULL constraint. But check if
> this could work for you:
>
> == 8< ===
> ALTER TABLE your_table ADD CONSTRAINT my_constr1 CHECK (your_column IS NOT NULL UNIQUE);
> ALTER TABLE your_table ADD CONSTRAINT my_constr2 UNIQUE (your_column);
> == 8< ===
>
>     It is not marked as "Primary Key", but it does the same job: checks
> that column is UNIQUE and NOT NULL.
>
>     Best regards,
> --
> --- Artur Pietruk, arturp@plukwa.net

--On mardi 23 juillet 2002 15:48 +0200 Raphael Bauduin
<raphael@be.easynet.net> wrote:

> Hi,
>
> In a data model change, I wanted to use the "create table as select"
> feature. However, I want that table to have a primary key defined, and
> thus a field defined as NOT NULL. I didn't find a way to add a NOT NULL
> constraint on a column of an existing table. If there isn't, what's the
> best way to go? A COPY?

why not just :
create table new_table (new fields with all you need);
insert into new_table <the same select as you had in your create as>;

--
Mathieu Arnold