Thread: about sql

1.how can i add constraint on existing table?
2.how can remove constraint from existing table?

please,reply quickly asap with querry.

thank you.............

use alter table command to do that.

alter table add constraint const_name .......

hi folks,

thanks for your reply..........

but i could not proceed that . i wil tell my clear position.

i have created one table with two columns (name ,id) without any constraint .now i want to create constraint for any one . already i have used your querry but did not work . the follwing message i got ''by using alter command you can't create or add column with constrint ''.

please ,very this quickly..................

hi,

well i will give the example:

SQL> create table d1( id number(10),
2 name varchar2(20));

this will create table d1 with columns id and name without any constraint. now for adding constraint:

SQL> alter table d1
2 add constraint id_primary_key primary key (id);

Table altered.

this adds the primary key constraint on column id, and constraint name is id_primary_key. for dropping this constraint, use the following:

SQL> alter table d1 drop constraint id_primary_key;

Table altered.


and one more thing, for creating constraints we use ALTER command but for not null we have to use MODIFY. like this:

SQL> ALTER TABLE d1 MODIFY (id number NOT NULL);

Table altered.

and go through, what are table and column level constraint, that will help u. and one more thing never create a constraint without giving them some name. because without any constraint name it will be difficult to drop the constraint.

cheers,

RSY

Originally Posted by raghav_sy

hi,

well i will give the example:

SQL> create table d1( id number(10),
2 name varchar2(20));

this will create table d1 with columns id and name without any constraint. now for adding constraint:

SQL> alter table d1
2 add constraint id_primary_key primary key (id);

Table altered.

this adds the primary key constraint on column id, and constraint name is id_primary_key. for dropping this constraint, use the following:

SQL> alter table d1 drop constraint id_primary_key;

Table altered.


and one more thing, for creating constraints we use ALTER command but for not null we have to use MODIFY. like this:

SQL> ALTER TABLE d1 MODIFY (id number NOT NULL);

Table altered.

and go through, what are table and column level constraint, that will help u. and one more thing never create a constraint without giving them some name. because without any constraint name it will be difficult to drop the constraint.

cheers,

RSY

--------------

hi thanks for your reply.......

again i could not proceed that .

the same problem occur.

the error message i got

''Cannot define PRIMARY KEY constraint on nullable column in table 'd1'.

and what is the meaning for 2 on querry?

and which version you r using myself is 2000.

i am waiting for your reply................

hi folks,

i got answer for that .

if we have to create constraint on exiting table for that the ecxat column should be not null.this is mondatory. then only we have add constraint.

but now my question is how to modify one column null to not null?

because,as a default if we creates the any table it will be nullable.so that we can't do create constraint.

but if we know about how to modify null to not null we can apply constrint.

and my request is that your querryu about modify did n't work..........

please,update your view.............?

hi

i got some idea but the modify querry not working well .when use that i got the follwing message.

guerry, ''alter table modify name varchar(20) not null''
error message, ''Incorrect syntax near 'varchar'.''

hi friend,

that is not the case, the null values in columns, create problem depending upon the type of constraint we are going to create.

if u are creating unique or check constraint on the column then null value in that case will not affect.

u faced the problem because there was a null value in the column and tried to create prmary key on that. so i will tell one thing,

if we create a primary key, it will automatically has a NOT NULL and unique constraint, so that NOT NULL feature of primary key was creating the problem.

now abt that modify query, it works, which quey i send in replies, first i test them then only i post them.

for u its not working because a NULL value is already present in that column. so u have to delete that value. then u can use the modify command. then it will work.

and coming to NOT NULL constraint, remember its a column level constraint.

ok
cheers,
RSY

hi,

the data type will not have any affect on query, both will work.

the only problem is that, in the query table name is missing. and even after using table name, the query is failing then please check the column values, it should not have any NULL values.

RSY

hi ragav

thanks for your reply,

but my table haven't any null values.

but i could not proceed ,..............

please one time your sellf and then give solution ..............

because i am trying a lot of way..............

please...........if is it possible please...........

hi

thank for your reply.........

i have only one data in this table .then where is possible to be null?

i confused about that.

if i use your querry i got the following message........

q : ''alter table b modify name not null''
m : ''Line 1: Incorrect syntax near 'name'.''

hi friend,

i do not know why u are getting the error, i did a lot of experiment but not able to get your error. for me all the queries are working.

its very difficult to understand where u have done the mistake. its better u create new table with specific constraints and then insert your data. drop the old table, and then rename the new table to old table name.

i know its not a good idea, but in real time there is not so much of time, and we have to work smarter rather then working hard without any ouput.

sorry for giving a bad suggestion, but sometimes its the reality.

Cheers,
RSY