SQL Help Please - Overclock.net

DanNEBTD · 07-04-07

Can any one tell me why SQL is saying invalid column name. . . I'm simply trying to add a NOT NULL CONSTRAINT

DanNEBTD · 07-04-07

illegal bump because if this is wrong then I have to change many other lines of code before moving on to indexes, sequences, etc. . . and its due tomorrow. . I have it written out but not compiled. . ..

version2 · 07-04-07

I don't know anything about constraints, but your sql query looks a little strange. Something like this?

Code:

ALTER TABLE Parents
 2 ADD CONSTRAINT First_Guard_Last_nn NOT NULL (First_Guard_Last),
 ADD CONSTRAINT First_Guard_Last_nn NOT NULL (First_Guard_Last);

What's with the "2"?

Edit: Actually I'm not quite sure even that would be right, syntax-wise.

DanNEBTD · 07-04-07

The second one is added when there is an error. . it repeats the erroneous code.

**BFRD** · 07-05-07

I *think* you can do:

ALTER TABLE Parents ADD CONSTRAINT First_Guard_Last_nn SET NOT NULL

I don't think you need the parens because you aren't doing a multiple column constraint.

DanNEBTD · 07-05-07

Ill try as soon as I can reconnect to the Terminal . .

and since I cant rep a mod Ill give you one of these:

e_dogg · 07-05-07

Are you using mySQL? I'm only familiar with MS SQL Server so take what I say with a grain of salt.

Try using square brackets around your column name like this: [First_Guard_Last] - or fully qualify it like this [Parents].[First_Guard_Last]

Also, since I'm not familiar with mySQL, I did some googling for you and came across this (see the synatx under section 3.1) - http://sql-info.de/en/mysql/referential-integrity.html

and this: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html

Are you sure your synatx is right?

From what I read in the 2nd link, it seems to me that your syntax should be:

ALTER TABLE parents MODIFY First_Guard_Last varchar2(15) not null

Toward the end of that 2nd link, there are a bunch of forum posts. One says:

Quote:

You can't drop a NOT NULL constraint on a column the way you can a foreign key or an index, or a default. Instead, just use the 'change' or 'modify' syntax and leave off the NOT NULL bit:

alter table table_name modify col_name bigint default null;

Any pre-existing indexes or foreign keys on the column are not affected.

Hope that helps!

DanNEBTD · 07-05-07

I modeled my syntax right from my book . . and Im using SQLPlus and have to so I cant use another compiler. . . thanks for the links Ill take a look at them.

e_dogg · 07-05-07

Ok, I assumed incorrectly that the output was from mySQL (the mySQL editor looks a lot like SQLPlus).

From this link: http://www.princeton.edu/~storacle/s...tutorial.shtml

Quote:

You can specify a new constraint on an existing table via the alter table command. The following example defines a new not null constraint on the table test_table

describe test_table;
alter table test_table modify (
full_name varchar2(64) constraint nn_test_fullname not null);
describe test_table;

Looks like you need the word "modify" in there. And it looks like the table name comes before the constraint name.

Hope that is more helpful.

DanNEBTD · 07-05-07

damn . . ive never seen that format before. . . hmm . . of course remote desktop doesnt work well over wireless too :-/ . . Ill check tomorrow I guess

		Overclock.net - Overclocking.net > Software, Programming and Coding > Coding and Programming
SQL Help Please

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)