Add an attribute to existing table

peter_budo · April 23rd, 2005, 08:16 AM

I am supposed to write an query which will add new attribute to my already existing table
So I written this

ALTER TABLE member
ADD e_mail CHAR(20)
which is fine, however when I try insert required data for each row I get stuck

INSERT INTO member(e_mail) VALUES('[email protected]');

Can anyone help me please....

P.T.

Imar · April 24th, 2005, 03:01 AM

What error do you get? Can you see the new column in the program you manage your tables with? (E.g. Enterprise Manager for Sql Server).

How do your other columns look like? Are you supposed to supply required information for those columns?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

peter_budo · April 24th, 2005, 05:25 AM

Table before adding new attribute

Code:

SQL> SELECT * FROM member;

MEMBE NAME                 ADDRESS                        TELEPHONE_NO STATUS
----- -------------------- ------------------------------ ------------ --------------------
A2345 John White           81 George Street,              020-84561122 Staff
                           London

R3456 Julie Lee            63 Ashby Street,               0121-8221024 Researcher
                           Edinburgh

S4567 David Ford           2 Elm Place, London            020-75511003 Student
S7654 Mary Howe            28 Malvern Street,             01322-451399 Student
                           Dartford

then I call my script

Code:

ALTER TABLE member
ADD e_mail CHAR(20);

Table adjusted as you see bellow

Code:

SQL> SELECT * FROM member;

MEMBE NAME                 ADDRESS                        TELEPHONE_NO STATUS               E_MAIL
----- -------------------- ------------------------------ ------------ -------------------- --------------------
A2345 John White           81 George Street,              020-84561122 Staff
                           London

R3456 Julie Lee            63 Ashby Street,               0121-8221024 Researcher
                           Edinburgh

S4567 David Ford           2 Elm Place, London            020-75511003 Student
S7654 Mary Howe            28 Malvern Street,             01322-451399 Student
                           Dartford

but when I add next line to fill new attribute with data

Code:

ALTER TABLE member
ADD e_mail CHAR(20)

INSERT INTO member(e_mail) VALUES('[email protected]');

I will get following error

Code:

SQL> @Q13.sql
Input truncated to 55 characters
INSERT INTO member(e_mail) VALUES('[email protected]')
            *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("W0411758"."MEMBER"."MEMBER_NO")

What it is trying to tell me???
w0411758 is my student ID, MEMBER is name of the table but why is there MEMBER_NO when I asked to insert new value into the E_MAIL????

P.T.

Imar · April 24th, 2005, 05:30 AM

Quote:

quote:but why is there MEMBER_NO when I asked to insert new value into the E_MAIL????

I don't know, really. Maybe there is an additional column called MEMBER_NO that you don't know off?

If that column is there, and it's a required column, then the error message makes sense. When you insert a new record using INSERT then you'll need to provide values for all required columns (unless they have a default value).....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

peter_budo · April 24th, 2005, 05:37 AM

As you see MEMBER_NO is primary key

Code:

CREATE TABLE member (
member_no        CHAR(5)     NOT NULL,
name            CHAR(20)    NOT NULL,
address            CHAR(30)    NOT NULL,
telephone_no        VARCHAR2(12),
status            CHAR(20),
PRIMARY KEY (member_no));

and default how do I set up them?

I went for some tutorials but all what they show is add new attribute but they they do not provide example for adding data for this attribute...

P.T.

Imar · April 24th, 2005, 05:46 AM

In that case, the error message makes sense, don't you think?? You *have* to provide a value for columns that have NOT NULL because that makes them required.

So, instead of specifying the email address, specify all required columns:

INSERT INTO member (member_no, name, address, Other Columns) (VALUES YourValuesGoHere)

How you setup defaults depend on the database you're using. For Sql server you can set them in the Properties grid for a selected column, or through CREATE code. Look in the help for info. For a CHAR(5) that's also the primary key, a default won't do you any good (unless you call some function that generates the key). I think you'll need to somehow generate a valid and unique 5 character member_no for each record you try to insert.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Can't Stop Now by Keane (Track 8 from the album: Hopes and Fears) What's This?

peter_budo · April 24th, 2005, 05:52 AM

All is "funny", because this is only table which has all data in expect that silly e_mail which I am supposed to add. I was trying to find out how to fill this attribute without touching rest of table.
I know it I can overwrite whole table and I just try to be little bit more smart than rest of student.

But as you pointed out I have to provide data because is NULL, is this also necessary if this data already exist???
P.T.

Imar · April 24th, 2005, 06:00 AM

Aha, in that case, use UPDATE instead of INSERT. As it name implies, UPDATE will update existing records, whereas INSERT always creates a brand new record.

UPDATE Member SET Email = 'SomeEmailAddress'

Note: this will update ALL records in the Member table and change the email address. If you want to limit the update to one record, add a WHERE clause:

UPDATE Member SET Email = 'SomeEmailAddress' WHERE Member_No = 'ABCDE'

HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: This Is The Last Time by Keane (Track 2 from the album: Hopes and Fears) What's This?

peter_budo · April 24th, 2005, 06:05 AM

That's make sense!!!!!!!!!
I did not try to use this because was badly explain by my tutor.
What I mean he just updated already existing for example change telephone number.

Thank you very much:D and have nice day

P.T.

peter_budo · April 24th, 2005, 01:35 PM

Yes on end of day it is working beautifully!!!!!!!
And for anybody with similar problem here is my code, I don't say is perfect but does what I need it to do with it

Code:

ALTER TABLE member
ADD e_mail CHAR(20);

UPDATE member SET e_mail='[email protected]' WHERE member_no='A2345';
UPDATE member SET e_mail='[email protected]' WHERE member_no='R3456';
UPDATE member SET e_mail='[email protected]' WHERE member_no='S4567';
UPDATE member SET e_mail='[email protected]' WHERE member_no='S7654';
UPDATE member SET e_mail='[email protected]' WHERE member_no='A7412';
UPDATE member SET e_mail='[email protected]' WHERE member_no='B6421';
UPDATE member SET e_mail='[email protected]' WHERE member_no='A1246';
UPDATE member SET e_mail='[email protected]' WHERE member_no='R8916';
UPDATE member SET e_mail='[email protected]' WHERE member_no='S1248';

P.T.