I do not work the tables made in MySQL on Microsoft SQL Server 2014

Question

I do not work the tables made in MySQL on Microsoft SQL Server 2014

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

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 SET option recognized. Mens. 102, Level 15, State 1, Line 53 Syntax incorrect near ','. Mens. 156, Level 15, State 1, Line 66 Incorrect syntax near the keyword 'RESTRICT'. Mens. 156, Level 15, State 1, Line 74 Incorrect syntax near the word key 'RESTRICT'. Mens. 156, Level 15, State 1, Line 82 Syntax incorrect close to the keyword 'RESTRICT'.

mysql sql sql-server

asked by Anónimo 04.01.2017 в 21:43

source

2 answers

1

More that they do not work 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 league that can help you a little more, I hope it will be helpful.

answered by 04.01.2017 в 22:13

How to get the servlet response html code immutableJs Update object key with second order of Map

score 0 · Accepted Answer

Although both MySql and SQL Server use SQL as a language they have their differences in terms of syntax, in your case, only some adjustments are required for your script to 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 do not have knowledge of any sentence that performs a task similar to MySQL's RESTRICT , if someone complements this answer, any comment is welcome. Meanwhile, your script is already functional in SQL Server.