--************************** DDL FOR COLLATION UCS_BASIC **************************

SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE 'localhost:D:\MYDB_UTF8.FDB' USER 'SYSDBA' PASSWORD 'A'
PAGE_SIZE 4096
DEFAULT CHARACTER SET UTF8 COLLATION UCS_BASIC;

CREATE TABLE USUARIO (
    CODIGO         INTEGER NOT NULL,
    USUARIO        VARCHAR(20) CHARACTER SET UTF8 NOT NULL COLLATE UCS_BASIC,
    SENHA          VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UCS_BASIC,
    CODCIDADE      INTEGER,
    ACESSOSISTEMA  CHAR(1) CHARACTER SET UTF8 COLLATE UCS_BASIC,
    CPF            VARCHAR(12) CHARACTER SET UTF8 COLLATE UCS_BASIC
);

ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO UNIQUE (USUARIO);
ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO_CPF UNIQUE (CPF);

ALTER TABLE USUARIO ADD CONSTRAINT PK_USUARIO PRIMARY KEY (CODIGO);





--************************** DDL FOR COLLATION UNICODE **************************

SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE 'localhost:D:\MYDB_UTF8.FDB' USER 'SYSDBA' PASSWORD 'A'
PAGE_SIZE 4096
DEFAULT CHARACTER SET UTF8 COLLATION UNICODE;

CREATE TABLE USUARIO (
    CODIGO         INTEGER NOT NULL,
    USUARIO        VARCHAR(20) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE,
    SENHA          VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE,
    CODCIDADE      INTEGER,
    ACESSOSISTEMA  CHAR(1) CHARACTER SET UTF8 COLLATE UNICODE,
    CPF            VARCHAR(12) CHARACTER SET UTF8 COLLATE UNICODE
);

ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO UNIQUE (USUARIO);
ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO_CPF UNIQUE (CPF);

ALTER TABLE USUARIO ADD CONSTRAINT PK_USUARIO PRIMARY KEY (CODIGO);





--************************** DDL FOR COLLATION UNICODE_CI **************************

SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE 'localhost:D:\MYDB_UTF8.FDB' USER 'SYSDBA' PASSWORD 'A'
PAGE_SIZE 4096
DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI;

CREATE TABLE USUARIO (
    CODIGO         INTEGER NOT NULL,
    USUARIO        VARCHAR(20) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI,
    SENHA          VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI,
    CODCIDADE      INTEGER,
    ACESSOSISTEMA  CHAR(1) CHARACTER SET UTF8 COLLATE UNICODE_CI,
    CPF            VARCHAR(12) CHARACTER SET UTF8 COLLATE UNICODE_CI
);

ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO UNIQUE (USUARIO);
ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO_CPF UNIQUE (CPF);

ALTER TABLE USUARIO ADD CONSTRAINT PK_USUARIO PRIMARY KEY (CODIGO);





--************************** DDL FOR COLLATION UNICODE_CI_AI **************************

SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE 'localhost:D:\MYDB_UTF8.FDB' USER 'SYSDBA' PASSWORD 'A'
PAGE_SIZE 4096
DEFAULT CHARACTER SET UTF8 COLLATION UNICODE_CI_AI;

CREATE TABLE USUARIO (
    CODIGO         INTEGER NOT NULL,
    USUARIO        VARCHAR(20) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI,
    SENHA          VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UNICODE_CI_AI,
    CODCIDADE      INTEGER,
    ACESSOSISTEMA  CHAR(1) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI,
    CPF            VARCHAR(12) CHARACTER SET UTF8 COLLATE UNICODE_CI_AI
);

ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO UNIQUE (USUARIO);
ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO_CPF UNIQUE (CPF);

ALTER TABLE USUARIO ADD CONSTRAINT PK_USUARIO PRIMARY KEY (CODIGO);





--************************** DDL FOR COLLATION UTF8 **************************

SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE 'localhost:D:\MYDB_UTF8.FDB' USER 'SYSDBA' PASSWORD 'A'
PAGE_SIZE 4096
DEFAULT CHARACTER SET UTF8 COLLATION UTF8;

CREATE TABLE USUARIO (
    CODIGO         INTEGER NOT NULL,
    USUARIO        VARCHAR(20) CHARACTER SET UTF8 NOT NULL COLLATE UTF8,
    SENHA          VARCHAR(10) CHARACTER SET UTF8 NOT NULL COLLATE UTF8,
    CODCIDADE      INTEGER,
    ACESSOSISTEMA  CHAR(1) CHARACTER SET UTF8 COLLATE UTF8,
    CPF            VARCHAR(12) CHARACTER SET UTF8 COLLATE UTF8
);

ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO UNIQUE (USUARIO);
ALTER TABLE USUARIO ADD CONSTRAINT UNQ_USUARIO_CPF UNIQUE (CPF);

ALTER TABLE USUARIO ADD CONSTRAINT PK_USUARIO PRIMARY KEY (CODIGO);




--************************** INSERT **************************

INSERT INTO USUARIO (CODIGO, USUARIO, SENHA, CODCIDADE, ACESSOSISTEMA, CPF) VALUES (1, 'TESTE1', 'A', NULL, 'S', '1');
INSERT INTO USUARIO (CODIGO, USUARIO, SENHA, CODCIDADE, ACESSOSISTEMA, CPF) VALUES (2, 'TESTE2', 'A', 5406, 'N', '2');
INSERT INTO USUARIO (CODIGO, USUARIO, SENHA, CODCIDADE, ACESSOSISTEMA, CPF) VALUES (3, 'TESTE3', 'A', 5406, 'S', '3');
INSERT INTO USUARIO (CODIGO, USUARIO, SENHA, CODCIDADE, ACESSOSISTEMA, CPF) VALUES (4, 'TESTE4', 'A', 5406, 'S', '4');
INSERT INTO USUARIO (CODIGO, USUARIO, SENHA, CODCIDADE, ACESSOSISTEMA, CPF) VALUES (5, 'TESTE5', 'A', 5406, 'S', '5');
COMMIT;