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 Aarusa,

The constraint can be dropped using ALTER TABLE command,
ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>

The above command is the generic command for adding a constraint to an existing table, but one small correction in the previous add constraint command, table_name is missing. Just add table_name to the command & try to add different types of constraints.

*** Innila ***

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..................

But Aarusa, u havent mentioned which type of constraint u wanted to add, either primary key or foreign key, etc...
Different Constraints has different alter commands.
Rather than giving all the commands by myself here, I feel u should understand the constraint types, which will help u a lot.
The following link gives simple & easy to understand explanation about SQL Server Constraints.
[URL="http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=53&rl=1"]SQL Server Constraints[/URL]

Hopefully it helps u, for further doubts, feel free to ask :-)

*** Innila ***

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

[QUOTE=raghav_sy;7683]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[/QUOTE]


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

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................

Well, [B]aarusa[/B] this is the conceptual error done by you.
Primary Key Column can not be NULL. That is not allowed in any RDBMS.

Now use this command to make your column NOT NULL first.
[CODE]
ALTER TABLE table_name
MODIFY column_name column_type;

Eg: ALTER TABLE emp
MODIFY id varchar(25) NOT NULL;[/CODE]

Then try creating the PRIMARY KEY constraint.

Cheers!
Kalayama

hi friend,

do not pay attention to that 2 in the query i wrote, actually i run that command on sql plus and copied from there, so that 2 is the line number. so neglect that 2.

i think some data is present in the columns on which u want to put primary key constraint and may be some value in that column is a NULL value.check this thing.

ok
RSY

hi folks,

i got answer for that .

if we have to create constraint on exiting table for that the ecxat column should be [COLOR="Red"]not null[/COLOR].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.............?

Well aarusa, please chek my previous post in this thread. I have already told the problem as well as the solution.
[QUOTE]
Well, aarusa this is the conceptual error done by you.
Primary Key Column can not be NULL. That is not allowed in any RDBMS.

Now use this command to make your column NOT NULL first.
Code:
ALTER TABLE table_name
MODIFY column_name column_type;

Eg: ALTER TABLE emp
MODIFY id varchar(25) NOT NULL;
Then try creating the PRIMARY KEY constraint.

Cheers!
Kalayama[/QUOTE]


Moreover, it was a very nice link posted by mangaivarma which explained the basics of constraints. Kindly read [B]ALL[/B] the replies for your question and then if your question is still unanswered, only then post your new question.
-Kalayama

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

[QUOTE]guerry, ''alter table modify name varchar(20) not null''[/QUOTE]

Well, your query is wrong because you haven't specified the table name there. The problem is because of that.

try like,
alter table [B]table_name[/B] modify name varchar(20) not null

It will have to work.

-Kalayama

Aarusa, the table name is missing in the above command, i think u've missed while posting.
u can use the command mentioned by Kalayama.
u can also try the Alter table command with or without the datatype,
ALTER TABLE <table_name> MODIFY <column_name> NOT NULL
This will modify the column constraint.

I have another doubt, have u inserted any null value(s) in the table.

*** Innila ***

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...........

Dude! Please check the other posts too dude! Check my previous post and mangaivarma's post.

Your previous ALTER TABLE <table_name> MODIFY <column_name> command was [B]WRONG[/B].

Your question is already answered. Please check the previous posts in this thread!

-Kalayama

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

Yes Aarusa, u can try Raghav's suggestion, so that u overcome the error hurdle which ur facing now.
Or else, try deleting the existing records of the table & try to modify the column. Try to get rid of the error first using the suggestions given in the thread.

*** Innila ***

hi ragav

sorry for the distrubends

i have posted what i created and what i have done

please check that and give solution


''[COLOR="Red"]create table c (name varchar(20),id int)

insert into c values('dss',32)

alter table c modify name not null[/COLOR]''

hi friend,

what ever u did is working on my system, i do not know what has happened to it on your system. well i executed the same thing on my system, and see the result below:

SQL> create table c (name varchar(20),id int);

Table created.

SQL> insert into c values('dss',32);

1 row created.

SQL> alter table c modify name not null;

Table altered.

SQL> desc c;
Name Null? Type
------------------------------- -------- ----
NAME NOT NULL VARCHAR2(20)
ID NUMBER(38)

well see the constraint is created.

i do not know why its not getting created on your system.

ok
RSY

hi ragav

thnaks for your commitment

i can add or drop constraint when creates the table with not null .

but i did n't modify null to not null or vies versa.

i have one doubt that will versions play mojor role about querry?

this is my problem.........

ok if is time permits ..............update your view .


thank you all.............

I a certain. Versions [B]WILL NOT[/B] play a role here. This is a very basic command and it CAN NOT go wrong. I don't know what mistake you are doing... Can you just execute these commands step by step and post them with the output here?

(Refer the beautifel post by [B]raghav_sy[/B] for formatting your post).

I might be of some help then.

-Kalayama

Ok dude. We might as well have been wrong all along. Try this for making a null column not null.
[code]alter table c alter column name not null[/code]
also try, [code]alter table c alter column name varchar(25) not null[/code]

ps: unforunately i don't have sql server installed with me now. But msdn pages say the above syntax work. Please check and let me know.

-kalayama

hi,

sorry i went to have lunch.

well, the version will not have any effect.

and yes, Kalayama the query 'alter table c alter column name not null' will not work on Oracle, i am not sure about SQL server, as i never worked on it.

for not null we have to use Alter...... modify.....

well, it was a nice question, after so much of gap i referred to the basic concepts. and it was good to revise.

hope other people must have got the benefit.

cheers,
RSY

Well [B]Raghav[/B], it was a mistake from our part. Though I have worked on SQL Server , Oracle, MY SQL and SYBASE(Currently I'm more focused on this), Oracle has always been my favourite database.

The [B]alter table [/B]command with [B]modify[/B] clause, works well both in Oracle and SYBASE. I checked official Microsoft MSDN pages, there they mention [B]alter table ... alter column...[/B] syntax. No modify clause present. Just check it out.
[QUOTE]
ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name
{
ALTER COLUMN column_name
{
[ type_schema_name. ] type_name [ ( { precision [ , scale ]
| max | xml_schema_collection } ) ]
[ COLLATE collation_name ]
[ NULL | NOT NULL ]
| {ADD | DROP } { ROWGUIDCOL | PERSISTED }
| DROP NOT FOR REPLICATION
}
| [ WITH { CHECK | NOCHECK } ] ADD
{
<column_definition>
| <computed_column_definition>
| <table_constraint>
} [ ,...n ]
| DROP
{
[ CONSTRAINT ] constraint_name
[ WITH ( <drop_clustered_constraint_option> [ ,...n ] ) ]
| COLUMN column_name
} [ ,...n ]
| [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT
{ ALL | constraint_name [ ,...n ] }
| { ENABLE | DISABLE } TRIGGER
{ ALL | trigger_name [ ,...n ] }
| SWITCH [ PARTITION source_partition_number_expression ]
TO [ schema_name. ] target_table
[ PARTITION target_partition_number_expression ]
}
[ ; ]

<drop_clustered_constraint_option> ::=
{
MAXDOP = max_degree_of_parallelism
| ONLINE = {ON | OFF }
| MOVE TO { partition_scheme_name ( column_name ) | filegroup
| "default"}
}[/QUOTE]

Here's the link [URL="http://msdn2.microsoft.com/en-us/library/ms190273.aspx"]MSDN Ref[/URL]

So, all along we were giving Oracle syntax for SQL server.

-Kalayama

hi,

hey good thing to know kalayama. i read that MSDN link. and u are correct for sql server alter...modify is not used.

now aarusa will search for us.
sorry aarusa. i gave u the suggestion pertaining to oracle and u were working on SQL server.

sorry for troubling u. from next time i will check the Type of database.

good learning at the end.

cheers

RSY

hi folks,

i am aarusa,

once i would like to tell thnks for you all.

i got answer for that.

when i used this querry that works,

[COLOR="Red"]alter table <table_name> alter column <column_name> <data_type> not null.[/COLOR]

thanks for alll................and one more thing

1.[COLOR="Blue"]what is the use of index ? [/COLOR]
2.[COLOR="Magenta"]how can i create that give with one example?[/COLOR]

hi good morning friends,

Indexes are optional structures associated with tables used to speed query execution. an index is created if there is frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced frequently in the WHERE clause.

well it increases query speed but reduce update speed.

Oracle automatically update indexes.
Concatenated index can be max. on 16 columns.

well , its a very huge topic. so its not possible to write about it here. u will have to read it on net or go through books.

i will give very simple example:

SQL> desc d1;
Name Null? Type
------------------------------- -------- ----
ID NUMBER
NAME VARCHAR2(20)

SQL> create index id on d1(id);

Index created.

u can see this index features with this query:

SQL> select * from user_objects where object_name = 'ID';


SQL> drop index id;

Index dropped.

i will give a link some of the material is there:

[url]http://www.techonthenet.com/oracle/indexes.php[/url]

and its better u go through other material also, understans how index is created, then how it works during query execution. because there are around 32 types of indexes( i am not sure about this number).

and one more thing, what ever i am writing its for ORACLE.

regards,
RSY

Hi Aaarusa,
u were using MS-SQL 2000 i think..
U told as u created one table with 2 column, just

Execute this Query for Making existing Column from NULL to NOT NULL

Alter table <TableName>
Alter Column <ColumnName> Datatype<Size> not null

this'll work.. If this not works let me know.. For creating Constraint, let me know wat constraint u need..

Regards
Vijay :)

hi vasu.

i got answer for that.

now i want details about index

if is it possible please explain me wilth small example

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

hi folks,

can i see weather how many tables are in one data base by using querrry?

because if i want to see that i have to be go object browser .........

Hi aarusa,

this query'll give u list of all userdefined tables,
--to list all tables
select distinct ist.* from information_schema.tables as ist
inner join sysobjects as so on ist.table_name = so.name
where so.status > 0

-- to list all table name along with columns
select distinct isc.* from information_schema.columns as isc
inner join sysobjects as so on isc.table_name = so.name
where so.status >0
order by isc.table_name, isc.ordinal_position


u can even paste this query in shortcut keys like (eg., ctrl+4).. In later when ever u reqd u can get it easily...

Regards
vijay :)

Hi aarusa,
There are 2 kinds of Indexes, Clustered index and Non-Clustered Index.

Clustered index
Only one can create on One table
This'll get create when ever u were creating Constraint like primary Key or Unique


Non-Clustered index
Many can create on One table
This we can create many on the same table based on the Column (OR column combination if reqd)
Syntax:
Create Index <Ind_Name> On <TableName> (Column1)
Create Index <Ind_Name> On <TableName> (Column1, Column2, ....)

If Still more Reqd, give me the scenario and i'l explain u in detail as much i can
Regards
Vijay :)

no jusr use the easy way
(inside the table design in Enterprise Manager)
you can use constrait check in properties
and jost right like:
1- i='1'or i='2'
2- like[1-2]

thats mean just enetr the code with out alter but do that before enter the all data to table