Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

UTF8 UNICODE_CI collate can´t be used in foreing key constraint [CORE1989] #2426

Closed
firebird-automations opened this issue Jul 11, 2008 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Fabiano Rezende (fabiano.rezende)

Is related to QA353

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;

Commits: abaa76c

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

Please note important related (and not fixed) bug: CORE1997.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5 Beta 1 [ 10251 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA353 [ QA353 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

QA test added.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

QA Status: No test => Done successfully

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants