mirror of
https://github.com/MariaDB/server.git
synced 2025-02-09 23:24:11 +01:00
daabc5cc68
Add support of referential constraints directly in column defininions: create table t1 (id1 int primary key); create table t2 (id2 int references t1(id1)); Referenced field name can be omitted if equal to foreign field name: create table t1 (id int primary key); create table t2 (id int references t1); Until 10.5 this syntax was understood by the parser but was silently ignored. In case of generated columns this syntax is disabled at parser level by ER_PARSE_ERROR. Note that separate FOREIGN KEY clause for generated columns is disabled at storage engine level.
113 lines
3.7 KiB
PHP
113 lines
3.7 KiB
PHP
################################################################################
|
|
# inc/vcol_column_def_options.inc #
|
|
# #
|
|
# Purpose: #
|
|
# Testing different optional parameters specified when defining #
|
|
# a virtual column. #
|
|
# #
|
|
# #
|
|
#------------------------------------------------------------------------------#
|
|
# Original Author: Andrey Zhakov #
|
|
# Original Date: 2008-09-02 #
|
|
# Change Author: Oleksandr Byelkin (Monty program Ab)
|
|
# Date: 2009-03-24
|
|
# Change: Syntax changed
|
|
################################################################################
|
|
|
|
--echo #
|
|
--echo # Section 1. Wrong column definition options
|
|
--echo # - NOT NULL
|
|
--echo # - NULL
|
|
--echo # - DEFAULT <value>
|
|
--echo # - AUTO_INCREMENT
|
|
--echo # - [PRIMARY] KEY
|
|
|
|
--echo # NOT NULL
|
|
--error ER_PARSE_ERROR
|
|
create table t1 (a int, b int as (a+1) not null);
|
|
create table t1 (a int);
|
|
--error ER_PARSE_ERROR
|
|
alter table t1 add column b int as (a+1) not null;
|
|
drop table t1;
|
|
|
|
--echo # NULL
|
|
--error ER_PARSE_ERROR
|
|
create table t1 (a int, b int as (a+1) null);
|
|
create table t1 (a int);
|
|
--error ER_PARSE_ERROR
|
|
alter table t1 add column b int as (a+1) null;
|
|
drop table t1;
|
|
|
|
--echo # DEFAULT
|
|
--error ER_PARSE_ERROR
|
|
create table t1 (a int, b int as (a+1) default 0);
|
|
create table t1 (a int);
|
|
--error ER_PARSE_ERROR
|
|
alter table t1 add column b int as (a+1) default 0;
|
|
drop table t1;
|
|
|
|
--echo # AUTO_INCREMENT
|
|
--error ER_PARSE_ERROR
|
|
create table t1 (a int, b int as (a+1) AUTO_INCREMENT);
|
|
create table t1 (a int);
|
|
--error ER_PARSE_ERROR
|
|
alter table t1 add column b int as (a+1) AUTO_INCREMENT;
|
|
drop table t1;
|
|
|
|
--echo # [PRIMARY] KEY
|
|
--error ER_PARSE_ERROR
|
|
create table t1 (a int, b int as (a+1) key);
|
|
--error ER_PARSE_ERROR
|
|
create table t1 (a int, b int as (a+1) primary key);
|
|
create table t1 (a int);
|
|
--error ER_PARSE_ERROR
|
|
alter table t1 add column b int as (a+1) key;
|
|
--error ER_PARSE_ERROR
|
|
alter table t1 add column b int as (a+1) primary key;
|
|
drop table t1;
|
|
|
|
--echo # Section 2. Other column definition options
|
|
--echo # - COMMENT
|
|
--echo # - REFERENCES (only syntax testing here)
|
|
--echo # - STORED (only systax testing here)
|
|
create table t1 (a int, b int as (a % 2) comment 'my comment');
|
|
show create table t1;
|
|
describe t1;
|
|
drop table t1;
|
|
create table t1 (a int, b int as (a % 2));
|
|
alter table t1 modify b int as (a % 2) comment 'my comment';
|
|
show create table t1;
|
|
describe t1;
|
|
insert into t1 (a) values (1);
|
|
select * from t1;
|
|
insert into t1 values (2,default);
|
|
select a,b from t1;
|
|
create table t2 like t1;
|
|
show create table t2;
|
|
describe t2;
|
|
insert into t2 (a) values (1);
|
|
select * from t2;
|
|
insert into t2 values (2,default);
|
|
select a,b from t2;
|
|
drop table t2;
|
|
drop table t1;
|
|
|
|
create table t1 (a int, b int as (a % 2) persistent);
|
|
show create table t1;
|
|
describe t1;
|
|
insert into t1 (a) values (1);
|
|
select * from t1;
|
|
insert into t1 values (2,default);
|
|
select a,b from t1;
|
|
drop table t1;
|
|
|
|
|
|
create table t2 (a int);
|
|
create table t1 (a int, b int as (a % 2) persistent);
|
|
show create table t1;
|
|
drop table t1;
|
|
create table t1 (a int, b int as (a % 2));
|
|
--error ER_UNSUPPORTED_ACTION_ON_GENERATED_COLUMN
|
|
alter table t1 modify b int as (a % 2) persistent;
|
|
show create table t1;
|
|
drop table t1;
|