Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQLÓï¾äPART3

constraint Example:
1. grammer:
 create table [schema.]table
      (column datatype [DEFAULT expr]
     [column_constraint], ...
     [table_constraint] [,......]);
2. example of a column_level constraint:
create table employees(employee_id number(6) constraint emp_emp_id_pk primiary key, first_name varchar2(20), ...);
3. example of a table-level constraint:
create table employees(employee_id number(6), first_name varchar2(20), ... job_id varchar2(20) not null, constraint emp_emp_id_pk primary key(employee_id));
4. UNIQUE example
create table employees(employee_id number(6), last_name varchar2(20) not null,..., constraint emp_email_uk UNIQUE(email));
5. FOREIGN KEY constraint
create table employees(employee_id number(6), last_name varchar2(20),... constraint emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id), constraint emp_email_uk UNIQUE(email));
** foreign key constaint:
** on delete cascade: deletes the dependent rows in the child table when a row in the parent table is deleted.
** on delete set null: converts dependent foreign key values to null.
6. CHECK constraint
* not allowed: 1) references to CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns 2)calls to SYSDATE, UID, USER and USERENV functions. 3) queries that refer to other values in other rows
FINAL example on constraints:
CREATE TABLE employees
( employee_id NUMBER(6) CONSTRAINT emp_employee_id PRIMARY KEY
, first_name VARCHAR2(20)
, last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL
, email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL CONSTRAINT emp_email_uk UNIQUE
, phone_number VARCHAR2(20)
, hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL
, job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL
, salary NUMBER(8,2) CONSTRAINT emp_salary_ck CHECK (salary>0)
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6) CONSTRAINT emp_manager_fk REFERENCES employees (employe


Ïà¹ØÎĵµ£º

sql Ö´ÐÐ˳Ðò

SQL SelectÓï¾äÍêÕûµÄÖ´ÐÐ˳Ðò
2009-06-02 15:57
SQL SelectÓï¾äÍêÕûµÄÖ´ÐÐ˳Ðò£º1¡¢from×Ó¾ä×é×°À´×Ô²»Í¬Êý¾ÝÔ´µÄÊý¾Ý£»2¡¢where×Ó¾ä»ùÓÚÖ¸¶¨µÄÌõ¼þ¶Ô¼Ç¼ÐнøÐÐɸѡ£»3¡¢group by×Ӿ佫Êý¾Ý»®·ÖΪ¶à¸ö·Ö×飻4¡¢Ê¹Óþۼ¯º¯Êý½øÐмÆË㣻5¡¢Ê¹ÓÃhaving×Ó¾äɸѡ·Ö×飻
ÒÔϽáÂÛÊÇͨ¹ýÔÚSql Server2000ÉϵÄʵÑéµÃ³öµÄ½áÂÛ£¬ ......

[ת]SQLÐÐÁл¥»»

ÎÊÌ⣺¼ÙÉèÓÐÕÅѧÉú³É¼¨±í(tb)ÈçÏÂ:
ÐÕÃû ¿Î³Ì ·ÖÊý
ÕÅÈý ÓïÎÄ 74
ÕÅÈý Êýѧ 83
ÕÅÈý ÎïÀí 93
ÀîËÄ ÓïÎÄ 74
ÀîËÄ Êýѧ 84
ÀîËÄ ÎïÀí 94
Ïë±ä³É(µÃµ½ÈçϽá¹û)£º 
ÐÕÃû ÓïÎÄ Êýѧ ÎïÀí 
---- ---- --- ......

ORACLE PL/SQL ´¥·¢Æ÷(trigger)ѧϰ±Ê¼Ç

1¡¢´¥·¢Æ÷µÄ¸ÅÄî
´¥·¢Æ÷Ò²ÊÇÒ»ÖÖ´øÃûµÄPL/SQL¿é¡£´¥·¢Æ÷ÀàËÆÓÚ¹ý³ÌºÍº¯Êý£¬ÒòΪËüÃǶ¼ÊÇÓµÓÐÉùÃ÷¡¢Ö´ÐкÍÒì³£´¦Àí¹ý³ÌµÄ´øÃûPL/SQL¿é¡£Óë°üÀàËÆ£¬´¥·¢Æ÷±ØÐë´æ´¢ÔÚÊý¾Ý¿âÖв¢ÇÒ²»Äܱ»¿é½øÐб¾µØ»¯ÉùÃ÷¡£
¶ÔÓÚ´¥·¢Æ÷¶øÑÔ£¬µ±´¥·¢Ê¼þ·¢ÉúµÄʱºò¾Í»áÏÔʽµØÖ´Ðиô¥·¢Æ÷£¬²¢ÇÒ´¥·¢Æ÷²»½ÓÊܲÎÊý¡£
 
´´½¨´¥·¢Æ÷µÄÓï·¨È ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ