Answer / santosh kumar

Let me explain both sides of the coin bit more clearly.

Table level Constraint
===========================
1. Constraints are defined separately after the columns are defined.
2. While defining constraints at this level constraint name must be provided
3. Not null constraints can't be defined at this level.
4. Composite keys can be defined at this level only.

A table level constraint can see every column in the table.


Column level Constraint
===========================
1. Constraints are defined along with the columns.
2. So constraint name is not required.
3. Not null constraints can be defined at this level only(NOT NULL constraint can only apply to one column).
4. Composite keys can't be defined at this level .
5. Column level constraint is more clear syntactically and more meaningful.

A column level constraint has scope only to the column it is defined on.


The major difference between the two constraint is the scope. So far as the use of constraints (validating proper data) is concerned they are pretty much the same.


Any column level constraint (exception: not null) can be expressed at the table level - but the opposite is not true.
Go for a column level constraint if the constraint is in fact a column constraint else use a table constraint.

It is recommended to always use a column constraint if the constraint applies just to the column (and NOT a table constraint if it only applies to a single column) - you can only do it at the column level.

Answer / anil

ANS:
COLUMN LEVEL CONSTRAINT
If we provide constraint along with column while creating the table then it is column level constraint. For column level constraint a constraint name is not mandatory.

TABLE(ROW) LEVEL CONSTRAINT
If we provide constraint after providing all the columns then it is table level constraint.For table level constraint a constraint name is mandatory.

Advantage of table level constraint:
We can create composite PK and composite FK in table level constraints but we cant create these in column level constraints.

Example:
CREATE TABLE oracle_tab
(
ord_id  NUMBER,
pid NUMBER,
qty NUMBER(2),
ord_dt DATE NOT NULL, /*column level constraint(cant be created as table level constraint)*/
price NUMBER(9,1) DEFAULT 0, /*column level constraint(cant be created as table level constraint)*/
CONSTRAINT ord_pk PRIMARY KEY(ord_id,pid), /*table level(can be created either as table level constraint or column level)*/
CONSTRAINT ord_fk FOREIGN KEY(pid) REFERENCES products(pid),/*table level(can be created either as table level constraint or column level)*/
CONSTRAINT ord_ch CHECK(qty>0) /*table level(can be created either as table level constraint or column level)*/
);

Answer / selvi

Column level constraint can not refer other columns or sysdate, UID, currVal, nextVal or rownum, and this is limitation of column level constraint over table level constraint.

Answer / sravya

column level contains only one column whereas tablelevel contains group of columns

how can we repair a mysql table? : Sql dba

0 Answers  

---

How do I view output in sql developer?

0 Answers  

---

what is a composite primary key ? : Sql dba

0 Answers  

---

How to find last day of the month in sql query

4 Answers  

---

What is normalization in sql?

0 Answers  

---

Write the order of precedence for validation of a column in a table? I. Done using database triggers. Ii. Done using integarity constraints

0 Answers  

---

What is parameter substitution in sql?

0 Answers  

---

how many no of table can be join in a sql query.

4 Answers  

---

Dear All, Question for this Week Find out possible error(s) (either at compile time or at runtime) in the following PL/SQL block. State the reason(s) and correct the errors. Declare Cursor C1 is select ename, sal, comm from emp; Begin For i in C1 Loop If i.comm between 299 and 999 then Dbms_output.put_line(i.Ename || ‘ ** Good Commission’); Elsif i.comm > 999 then Dbms_output.put_line(i.Empno || ‘ ** Very Good Commission’); close C1; Else Dbms_output.put_line(i.Ename || ‘ ** ’ ||nvl(i.comm,‘O’)); End if; End Loop; End;

7 Answers   Accenture,

---

What do you understand by pl/sql cursors?

0 Answers  

---

What is the difference between anonymous block and named blocks?

3 Answers  

---

How are multiple column = value pairs delimited in the SET clause of an UPDATE statement? 1. With commas (SET price = 0, status = 'I') 2. With parentheses (SET (price = 0) (status = 'I')) 3. With double-pipes (SET price = 0 || status = 'I') 4. With square-brackets (SET [price = 0] [status = 'I'] 5. With single or multiple spaces (SET price = 0 status = 'I')

2 Answers  

---