Issue Details (XML | Word | Printable)

Key: CORE-1989
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Fabiano Rezende
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

UTF8 UNICODE_CI collate can´t be used in foreing key constraint

Created: 11/Jul/08 02:39 PM   Updated: 25/Apr/11 02:43 PM
Component/s: Charsets/Collation
Affects Version/s: 2.1.0
Fix Version/s: 2.5 Beta 1

Time Tracking:
Not Specified

Environment: Windows XP sp2
Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
Here is a test case to demonstrate the issue:

/******************************************************************************/
/* Generated by IBExpert 2008.05.08 11/07/2008 15:25:05 */
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES UTF8;

CREATE DATABASE 'C:\Databases\FB\TEST.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET UTF8;



/******************************************************************************/
/* Domains */
/******************************************************************************/

CREATE DOMAIN INT64 AS
BIGINT;

CREATE DOMAIN VARCHAR100 AS
VARCHAR(100)
COLLATE UNICODE_CI;

CREATE DOMAIN VARCHAR100_CS AS
VARCHAR(100)
COLLATE UNICODE;

CREATE DOMAIN VARCHAR30 AS
VARCHAR(30)
COLLATE UNICODE_CI;

CREATE DOMAIN VARCHAR30_CS AS
VARCHAR(30)
COLLATE UNICODE;



/******************************************************************************/
/* Tables */
/******************************************************************************/



CREATE TABLE ROLES (
    ROLE_NAME VARCHAR30 NOT NULL,
    APPLICATION_NAME VARCHAR100 NOT NULL
);


CREATE TABLE ROLES_CS (
    ROLE_NAME VARCHAR30_CS NOT NULL,
    APPLICATION_NAME VARCHAR100_CS NOT NULL
);


CREATE TABLE USERS (
    USER_NAME VARCHAR100 NOT NULL,
    APPLICATION_NAME VARCHAR100 NOT NULL
);


CREATE TABLE USERS_CS (
    USER_NAME VARCHAR100_CS NOT NULL,
    APPLICATION_NAME VARCHAR100_CS NOT NULL
);


CREATE TABLE USERS_IN_ROLES (
    USER_NAME VARCHAR100 NOT NULL,
    ROLE_NAME VARCHAR30 NOT NULL,
    APPLICATION_NAME VARCHAR100 NOT NULL
);


CREATE TABLE USERS_IN_ROLES_CS (
    USER_NAME VARCHAR100_CS NOT NULL,
    ROLE_NAME VARCHAR30_CS NOT NULL,
    APPLICATION_NAME VARCHAR100_CS NOT NULL
);




/******************************************************************************/
/* Primary Keys */
/******************************************************************************/

ALTER TABLE ROLES ADD CONSTRAINT PK_ROLES PRIMARY KEY (ROLE_NAME, APPLICATION_NAME);
ALTER TABLE ROLES_CS ADD CONSTRAINT PK_ROLES_CS PRIMARY KEY (ROLE_NAME, APPLICATION_NAME);
ALTER TABLE USERS ADD CONSTRAINT PK_USERS PRIMARY KEY (USER_NAME, APPLICATION_NAME);
ALTER TABLE USERS_CS ADD CONSTRAINT PK_USERS_CS PRIMARY KEY (USER_NAME, APPLICATION_NAME);


/******************************************************************************/
/* Foreign Keys */
/******************************************************************************/

ALTER TABLE USERS_IN_ROLES ADD CONSTRAINT FK_USERS_IN_ROLES_ROLES FOREIGN KEY (ROLE_NAME, APPLICATION_NAME) REFERENCES ROLES (ROLE_NAME, APPLICATION_NAME) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE USERS_IN_ROLES ADD CONSTRAINT FK_USERS_IN_ROLES_USERS FOREIGN KEY (USER_NAME, APPLICATION_NAME) REFERENCES USERS (USER_NAME, APPLICATION_NAME) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE USERS_IN_ROLES_CS ADD CONSTRAINT FK_USERS_IN_ROLES_ROLES_CS FOREIGN KEY (ROLE_NAME, APPLICATION_NAME) REFERENCES ROLES_CS (ROLE_NAME, APPLICATION_NAME) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE USERS_IN_ROLES_CS ADD CONSTRAINT FK_USERS_IN_ROLES_USERS_CS FOREIGN KEY (USER_NAME, APPLICATION_NAME) REFERENCES USERS_CS (USER_NAME, APPLICATION_NAME) ON DELETE CASCADE ON UPDATE CASCADE;



insert into USERS(USER_NAME, APPLICATION_NAME) values('User', 'App');
insert into USERS_CS(USER_NAME, APPLICATION_NAME) values('User', 'App');
insert into ROLES(ROLE_NAME, APPLICATION_NAME) values('Role', 'App');
insert into ROLES_CS(ROLE_NAME, APPLICATION_NAME) values('Role', 'App');
commit;
insert into USERS_IN_ROLES_CS(USER_NAME, ROLE_NAME, APPLICATION_NAME) values('User', 'Role', 'App');
commit;
-- Raises error
insert into USERS_IN_ROLES(USER_NAME, ROLE_NAME, APPLICATION_NAME) values('User', 'Role', 'App');
commit;


 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Adriano dos Santos Fernandes added a comment - 16/Jul/08 10:27 AM
Please note important related (and not fixed) bug: CORE-1997.

Pavel Cisar added a comment - 25/Apr/11 02:43 PM
QA test added.