CODEWITHSUNDEEP
mysqlsqldatabase
Novellizator
Novellizator

Reputation: 14883

Is mysql compatible with sql-92?

The other day I was trying to create a table in mysql usign a standard syntax:

create table client(
    rc character varying(11)
    constraint client_pk primary key 
);

However, mysql doesn't support setting a primary key this way. (I'm 99% sure it's compliant with the sql-92 norm) So my question: Does MySQL fail to support at even these basic things from a 20 year old standard, or am I missing something?

EDIT: my goal is NOT to create this table using using some mysql dialect, but to create this table using a standardized syntax. And the question is whether it is possible.

Upvotes: 6

Views: 4493

Answers (3)

spencer7593
spencer7593

Reputation: 108450

To answer your question, no, MySQL is not fully compliant with the SQL-92 specification, in that MySQL supports only a subset of the specification, and MySQL has some significant(ly useful) extensions which are not part of the SQL-92 specification.

This is not just about the SQL syntax that MySQL accepts and recognizes, but (the probably bigger issue) is about what MySQL actually does with the syntax, the actual operations that MySQL performs with the syntax that it does accept. That's really a more important issue than the more superficial issue of what syntax MySQL recognizes.

It's not that MySQL is lazy. And it's not that MySQL just doesn't care.

There is an entire section of the MySQL documentation devoted to MySQL differences from the SQL standard. And the most important differences aren't really about syntax, but rather about that fact that "MySQL performs operations differently in some cases."

For example, MySQL accepts syntax for CHECK constraints, but MySQL does not actually do any checking or make any attempt to enforce CHECK constraints. MySQL accepts syntax that meets the SQL-92 specification, but the behavior of MySQL is much different from other databases that accept that same syntax.

As another example, the behavior of MySQL with respect to FOREIGN KEY constraints differs significantly, depending on whether the storage engine for the table is MyISAM or InnoDB.

To get your CREATE TABLE statement to execute in MySQL, you would need to change it.

It looks like the easiest option for you would be to remove constraint client_pk from the sql text. You can either specify the table constraint in the column definition:

create table client(
   rc character varying(11) primary key 
);

or declare the table constraint separately"

create table client(
   rc character varying(11),
   primary key (rc)
);

Both of those forms are entirely compatible with the SQL-92 specification.

Note that MySQL does accept the syntax for constraint name definition, when the declaration of the constraint is not on the column, e.g.

create table client(
   rc character varying(11),
   constraint client_pk primary key (rc)
);

And that syntax is also entirely compatible with SQL-92 specifiction.

But note that MySQL ignores the supplied constraint name, and assigns the name PRIMARY to the primary key constraint. And this is true even if the constraint is declared in a separate ALTER TABLE statement.

Note that the syntax that MySQL accepts, and in some cases the operations it performs, is dependent on the settings of specific MySQL variables, in particular, sql_mode, and of particular concern, in the case of your statement, the setting of the default-storage-engine variable, and whether this table will be created using the MyISAM engine or the InnoDB engine, or even the corner case that default-storage-engine has been set to BLACKHOLE.

I understand that your goal is to "create this table with standardized syntax".

But I think you really DO need to be concerned with at least some minimum subset of MySQL specific syntax, in as far as the variety of possible behaviors that MySQL can exhibit when presented with "standardized syntax". Consider:

SET default-storage-engine = 'MyISAM' ;
SET default-storage-engine = 'InnoDB' ;
SET sql_mode = '' ; 
SET sql_mode = 'ANSI' ;

Upvotes: 6

Benny Hill
Benny Hill

Reputation: 6240

This works for me in MySQL 5.5.29:

create table client(
    rc character varying(11),
    constraint client_pk primary key(rc)
);

However, as a_horse_with_no_name has pointed out, MySQL ignores the client_pk name and calls it PRIMARY.

References:

http://savage.net.au/SQL/sql-92.bnf.html

http://owen.sj.ca.us/~rk/howto/sql92.html

Upvotes: 2

RajputAdya
RajputAdya

Reputation: 63

create table tablename
(
    column_Name varchar(20) not null,
    column_Name varchar(20),
    primary key(column_name)
);

In this way you can create table and set primary key.

Upvotes: 1

Related Questions