Home / Questions / 42409
Accepted
Anónimo
Asked: 2020-01-05 12:43:25 +0800 CST 2020-01-05 12:43:25 +0800 CST

Tables made in MySQL on Microsoft SQL Server 2014 do not work for me

  • 772
SET SESSION FOREIGN_KEY_CHECKS=0;

/* Drop Tables */

DROP TABLE IF EXISTS SE_COMPONE;
DROP TABLE IF EXISTS ARTICULO;
DROP TABLE IF EXISTS PEDIDO;
DROP TABLE IF EXISTS CLIENTE;




/* Create Tables */

CREATE TABLE ARTICULO
(
    num_serie numeric NOT NULL,
    id_articulo numeric,
    nombre_articulo varchar(100),
    PRIMARY KEY (num_serie),
    UNIQUE (num_serie)
);


CREATE TABLE CLIENTE
(
    dni varchar(12) NOT NULL,
    nombre_cliente varchar(40),
    apellidos varchar(50),
    PRIMARY KEY (dni),
    UNIQUE (dni)
);


CREATE TABLE PEDIDO
(
    fecha datetime NOT NULL,
    id_pedido numeric,
    nombre_pedido varchar(100),
    dni varchar(12) NOT NULL,
    PRIMARY KEY (fecha),
    UNIQUE (fecha),
    UNIQUE (dni)
);


CREATE TABLE SE_COMPONE
(
    fecha datetime NOT NULL,
    num_serie numeric NOT NULL,
    cantidad numeric,
    peso double,
    caducidad datetime,
    UNIQUE (fecha),
    UNIQUE (num_serie)
);



/* Create Foreign Keys */

ALTER TABLE SE_COMPONE
    ADD FOREIGN KEY (num_serie)
    REFERENCES ARTICULO (num_serie)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT
;


ALTER TABLE PEDIDO
    ADD FOREIGN KEY (dni)
    REFERENCES CLIENTE (dni)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT
;


ALTER TABLE SE_COMPONE
    ADD FOREIGN KEY (fecha)
    REFERENCES PEDIDO (fecha)
    ON UPDATE RESTRICT
    ON DELETE RESTRICT
;

It gives me the following errors:

Mens. 195, Level 15, State 7, Line 2 'SESSION' is not a recognized SET option. Mens. 102, Level 15, State 1, Line 53 Incorrect syntax around ','. Mens. 156, Level 15, State 1, Line 66 Incorrect syntax near keyword 'RESTRICT'. Mens. 156, Level 15, State 1, Line 74 Incorrect syntax near keyword 'RESTRICT'. Mens. 156, Level 15, State 1, Line 82 Incorrect syntax near keyword 'RESTRICT'.

mysql

2 Answers

  1. But that they do not work for you is that SQL Server does not recognize the RESTRICT statement because you have to specify what you want to do, for example:

    ALTER TABLE EmpEducation
ADD CONSTRAINT [FK_EmpEducation_Employees]
FOREIGN KEY (empno)REFERENCES employees(empno)
DELETE SET NULL ON UPDATE SET NULL
GO

    Here I leave you a link that can help you a little more, I hope it helps you.

    • 1
  2. Best Answer

    Although both MySql and SQL Server use SQL as a language they have their differences in terms of syntax, in your case, only a few adjustments are required to make your script work in SQL Server.

    BEGIN TRANSACTION
/* Drop Tables */
IF OBJECT_ID('SE_COMPONE', 'U') IS NOT NULL
    DROP TABLE SE_COMPONE;

IF OBJECT_ID('ARTICULO', 'U') IS NOT NULL
    DROP TABLE ARTICULO;

IF OBJECT_ID('PEDIDO', 'U') IS NOT NULL
    DROP TABLE PEDIDO;

IF OBJECT_ID('CLIENTE', 'U') IS NOT NULL
    DROP TABLE CLIENTE;

/* Create Tables */

CREATE TABLE ARTICULO
(
    num_serie numeric NOT NULL,
    id_articulo numeric,
    nombre_articulo varchar(100),
    PRIMARY KEY (num_serie),
    UNIQUE (num_serie)
);

CREATE TABLE CLIENTE
(
    dni varchar(12) NOT NULL,
    nombre_cliente varchar(40),
    apellidos varchar(50),
    PRIMARY KEY (dni),
    UNIQUE (dni)
);


CREATE TABLE PEDIDO
(
    fecha datetime NOT NULL,
    id_pedido numeric,
    nombre_pedido varchar(100),
    dni varchar(12) NOT NULL,
    PRIMARY KEY (fecha),
    UNIQUE (fecha),
    UNIQUE (dni)
);

CREATE TABLE SE_COMPONE
(
    fecha datetime NOT NULL,
    num_serie numeric NOT NULL,
    cantidad numeric,
    peso float,
    caducidad datetime,
    UNIQUE (fecha),
    UNIQUE (num_serie)
);

/* Create Foreign Keys */
ALTER TABLE SE_COMPONE
    ADD FOREIGN KEY (num_serie)
    REFERENCES ARTICULO (num_serie);

ALTER TABLE PEDIDO
    ADD FOREIGN KEY (dni)
    REFERENCES CLIENTE (dni);

ALTER TABLE SE_COMPONE
    ADD FOREIGN KEY (fecha)
    REFERENCES PEDIDO (fecha);

COMMIT

    Note: in SQL Server I am not aware of any statement that performs a task similar to that RESTRICTof MySql, if someone complements this answer, any comment is welcome. In the meantime, your scriptis already functional in SQL Server.

    • 0