userinfo.sql

来自「使用最广泛的radius的linux的源码」· SQL 代码 · 共 35 行

/*
 * $Id: userinfo.sql,v 1.2 2005/03/19 19:08:08 pnixon Exp $
 *
 */

/*
 * Table structure for table 'userinfo'
 */

CREATE TABLE userinfo (
	id		INT PRIMARY KEY,
	username	VARCHAR(128) DEFAULT '' NOT NULL,
	name		VARCHAR(128) DEFAULT '' NOT NULL,
	mail		VARCHAR(128) DEFAULT '' NOT NULL,
	department	VARCHAR(128) DEFAULT '' NOT NULL,
	workphone	VARCHAR(128) DEFAULT '' NOT NULL,
	homephone	VARCHAR(128) DEFAULT '' NOT NULL,
	mobile		VARCHAR(128) DEFAULT '' NOT NULL 
);
CREATE INDEX userinfo_department_idx ON userinfo (department);
CREATE INDEX userinfo_username_idx ON userinfo (username);
CREATE SEQUENCE userinfo_seq START WITH 1 INCREMENT BY 1;


/* Trigger to emulate a serial # on the primary key */
CREATE OR REPLACE TRIGGER userinfo_serialnumber
        BEFORE INSERT OR UPDATE OF id ON userinfo
        FOR EACH ROW
        BEGIN
                if ( :new.id = 0 or :new.id is null ) then
                        SELECT userinfo_seq.nextval into :new.id from dual;
                end if;
        END;
/