使用存储过程插入到具有外键的多个表中

Question

我正在尝试将新用户插入到我使用存储过程创建的以下数据库中。

过程如下：

DROP PROCEDURE IF EXISTS insert_user, insert_address, insert_new_user
GO
CREATE PROCEDURE insert_user(@name VARCHAR(20), @sex CHAR(1), @date_of_birth DATE, @account_type INT, @id_address INT) AS
BEGIN
DECLARE @position_user INT
INSERT INTO [user]([user_name],[User_Sex], [date_of_birth], [account_type], [id_address])
VALUES(@name, @sex, @date_of_birth, @account_type, @id_address)
SELECT @position_user = @@IDENTITY
SELECT @id_address = IDENT_CURRENT('address')
END
GO
CREATE PROCEDURE insert_address(@street VARCHAR(255), @number INT, @locality VARCHAR(255), @city VARCHAR(255), @country_code CHAR(2) ) AS
BEGIN
DECLARE @position_address INT
INSERT INTO [address]([street], [number], [locality], [city], [country_code])
VALUES(@street, @number, @locality, @city, @country_code)
SELECT @position_address = @@IDENTITY
END
go
CREATE PROCEDURE insert_new_user(@name VARCHAR(20), @sex CHAR(1), @date_of_birth DATE, @account_type INT, @street VARCHAR(255), @number INT, @locality VARCHAR(255), @city VARCHAR(255), @country_code CHAR(2) )
AS
BEGIN
DECLARE @id_user INT
SELECT @id_user = [user].[id_user]
FROM [user]
WHERE [user].[user_name] = @name
IF (@id_user IS NULL) 
BEGIN
EXEC insert_user @name, @sex, @date_of_birth, @account_type, @id_user
end
EXEC insert_address @street, @number, @locality, @city, @country_code
END
GO

这给了我错误：

Msg 515，级别 16，状态 2，过程 insert_user，第 5 行 无法将值 NULL 插入到表 'id_address' 列 'dance_partner.dbo.user' 中;列不允许空值。插入失败。

如果我将字段"id_address"设置为 NULL，则过程"有效"，但 [user] 表中的字段"id_address"设置为 NULL，我不明白，因为我试图让字段"id_address"引用 [地址] 表的最后一个 id

SELECT @id_address = IDENT_CURRENT('address')

我错过了什么？

USE [master]
DROP DATABASE IF EXISTS [dance_partner];
GO
CREATE DATABASE [dance_partner];
GO
USE [dance_partner];
GO
CREATE TABLE [user](
[id_user] INT PRIMARY KEY NOT NULL IDENTITY(1,1),
[user_name] VARCHAR(45)  NOT NULL UNIQUE,
[User_Sex] CHAR(1) NOT NULL,
[date_of_birth] DATE NOT NULL,
[account_type] INT NOT NULL,
[id_address] INT NOT NULL,
);
GO
CREATE TABLE [address](
[id_address] INT PRIMARY KEY NOT NULL IDENTITY(1,1),
[street] VARCHAR(255) NOT NULL,
[number] INT NOT NULL,
[locality] VARCHAR(255) NOT NULL,
[city] VARCHAR(255) NOT NULL,
[country_code] CHAR(2) NOT NULL
);
GO
CREATE TABLE [membership](
[account_type] INT PRIMARY KEY NOT NULL IDENTITY(1,1),
[membership_name] VARCHAR(45) UNIQUE NOT NULL,
[membership_price] DECIMAL(4,2) NOT NULL
);
GO
CREATE TABLE [style](
[style_ref] INT PRIMARY KEY NOT NULL IDENTITY(1,1),
[style_name] VARCHAR(45) UNIQUE NOT NULL
);
GO
CREATE TABLE [dance](
[id_dance] INT NOT NULL IDENTITY(1,1),
[dancer_1_id_user] INT,
[dancer_2_id_user] INT,
[dance_dtg] DATETIME NOT NULL,
[style_ref] INT NOT NULL,
FOREIGN KEY (dancer_1_id_user) REFERENCES [user] (id_user),
FOREIGN KEY (dancer_2_id_user) REFERENCES [user] (id_user),
FOREIGN KEY (style_ref) REFERENCES [style] (style_ref)  
);
GO
CREATE TABLE [user_dance_style](
[id_user] INT,
[style_ref] INT NOT NULL
FOREIGN KEY (id_user) REFERENCES [user] (id_user),
FOREIGN KEY (style_ref) REFERENCES [style] (style_ref)
)

ALTER TABLE [user]
ADD CONSTRAINT fk_user_memebership FOREIGN KEY (account_type)
REFERENCES membership (account_type),
CONSTRAINT fk_user_address FOREIGN KEY (id_address)
REFERENCES address (id_address);
-- disable all constraints
EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
INSERT INTO [membership] ([membership_name], [membership_price])
VALUES 
('free', '0'),
('regular', '15'),
('premium', '30')
GO
INSERT INTO [style]([style_name])
VALUES
('Salsa'),
('Bachata'),
('Kizomba')
GO
INSERT INTO [user] ([user_name], [User_Sex], [date_of_birth], [account_type], [id_address])
VALUES 
('sara', 'f', '1990-04-23', '1', '1'),
('elenor', 'f', '1989-02-18', '1', '2'),
('eva', 'f', '1987-01-04','1','3'),
('mike', 'm', '1985-05-02', '1', '4'),
('phil', 'm', '1985-03-01', '1', '5'),
('laurent', 'm', '1986-02-14', '2', '6'),
('nidia', 'f', '1985-01-16', '2', '7'),
('franz', 'm', '1990-03-17', '2', '8'),
('stephan', 'm', '1991-05-23', '2', '9'),
('sandra', 'f', '1993-03-25', '3', '10'),
('virginie', 'f', '1999-05-03', '3', '11'),
('claire', 'f', '1992-02-24', '3', '12'),
('laurence', 'f', '1991-04-26', '3', '13'),
('pierre', 'm', '1987-02-14', '3', '14'),
('thierry', 'm', '1989-01-04', '3', '15'),
('nancy', 'f', '1950-04-15', '1', '16'),
('cédric', 'm', '1980-02-02', '1', '17')
GO
INSERT INTO [address] ([street], [number], [locality], [city], [country_code])
VALUES
('av de l''exposition', '13', 'laeken', 'bruxelles', 'be'),
('rue cans', '2', 'ixelles', 'bruxelles', 'be'),
('rue goffart', '32', 'ixelles', 'bruxelles', 'be'),
('ch de haecht', '17', 'schaerbeek', 'bruxelles', 'be'),
('rue metsys', '108', 'schaerbeek', 'bruxelles', 'be'),
('rue du pré', '223', 'jette', 'bruxelles', 'be'),
('rue sergent sorenser', '65', 'ganshoren', 'bruxelles', 'be'),
('rue d''aumale', '38', 'anderlecht', 'bruxelles', 'be'),
('av de fré', '363', 'uccle', 'bruxelles', 'be'),
('rue de lisbonne', '52', 'saint gilles', 'bruxelles', 'be'),
('av neptune', '24', 'forest', 'bruxelles', 'be'),
('av mozart', '76', 'forest', 'bruxelles', 'be'),
('rue emile delva', '92', 'laeken', 'bruxelles', 'be'),
('av de la chasse', '68', 'etterbeek', 'bruxelles', 'be'),
('rue leopold 1', '42', 'laeken', 'bruxelles', 'be'),
('av charle woeste', '68', 'jette', 'bruxelles', 'be'),
('ch de boondael', '12', 'ixelles', 'bruxelles', 'be')
GO
INSERT INTO [user_dance_style] ([id_user], [style_ref])
VALUES
(1, 1),(1, 2),(1, 3),(2, 1),(2, 2),(2, 3),(3, 1),(3, 2),(4, 1),(4, 2),
(4, 3),(5, 2),(5, 3),(6, 1),(7, 3),(8, 3),(9, 1),(9, 2),(9, 3),(10, 1),
(10, 2),(10, 3),(11, 3),(12, 2),(13, 2),(14, 1),(15, 3),(16, 1)
GO
INSERT INTO [dance]([dancer_1_id_user], [dancer_2_id_user], [dance_dtg], [style_ref])
VALUES
(1, 2, convert(datetime, '2019-11-24 10:34:09 PM',20), 3),
(4, 2, convert(datetime, '2019-11-24 10:50:00 PM',20), 3),
(3, 5, convert(datetime, '2019-11-24 10:35:00 PM',20), 2),
(6, 1, convert(datetime, '2019-11-24 10:37:00 PM',20), 1),
(7, 2, convert(datetime, '2019-11-24 10:37:00 PM',20), 3),
(8, 1, convert(datetime, '2019-12-03 11:20:03 PM',20), 3),
(9, 3, convert(datetime, '2019-12-23 10:45:00 AM',20), 1),
(10, 12, convert(datetime, '2019-12-26 11:20:00 AM',20), 2),
(11, 4, convert(datetime, '2020-01-02 08:45:00 AM',20), 3),
(12, 5, convert(datetime, '2020-01-02 11:10:04 AM',20), 2),
(13, 12, convert(datetime, '2020-02-04 09:25:00 PM',20), 2),
(14, 10, convert(datetime, '2020-02-25 10:45:00 AM',20), 1),
(2, 14, convert(datetime, '2020-02-25 08:45:00 PM',20), 1),
(5, 10, convert(datetime, '2020-03-01 11:15:06 AM',20), 2),
(17, 2, convert(datetime, '2020-03-04 03:15:06 AM',20), 1)
GO

Answer 1

这是纠正您面临的问题的代码。

1. 您正在猜测新的地址记录 ID 是什么，并在它存在之前将其插入到用户表中。这是非常糟糕的做法，不仅可能会在某些时候失败，而且您没有使用外键来强制关系 - 这意味着随着时间的推移，您的数据库中可能会有错误的数据。
2. 从存储过程获取单个值的最简单方法是使用OUT参数。
3. 按照参照完整性规则，您需要先插入地址记录，以便它存在(如果您有外键，则会强制执行(。

完整代码如下。

CREATE PROCEDURE insert_user
(
@name VARCHAR(20)
, @sex CHAR(1)
, @date_of_birth DATE
, @account_type INT
, @address_id INT
, @user_id INT OUT
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [user]([user_name],[User_Sex], [date_of_birth], [account_type], [id_address])
VALUES(@name, @sex, @date_of_birth, @account_type, @id_address);
-- If we inserted a row, get the new ID
if @@ROWCOUNT = 1 set @user_id = SCOPE_IDENTITY();
RETURN 0;
END
go
CREATE PROCEDURE insert_address
(
@street VARCHAR(255)
, @number INT
, @locality VARCHAR(255)
, @city VARCHAR(255)
, @country_code CHAR(2)
, @address_id INT OUT
)
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [address]([street], [number], [locality], [city], [country_code])
VALUES(@street, @number, @locality, @city, @country_code);
-- If we inserted a row, get the new ID
if @@ROWCOUNT = 1 set @address_id = SCOPE_IDENTITY();
RETURN 0;
END
go
CREATE PROCEDURE insert_new_user
(
@name VARCHAR(20)
, @sex CHAR(1)
, @date_of_birth DATE
, @account_type INT
, @street VARCHAR(255)
, @number INT
, @locality VARCHAR(255)
, @city VARCHAR(255)
, @country_code CHAR(2)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @address_id INT, @user_id INT;
EXEC insert_address @street, @number, @locality, @city, @country_code, @address_id OUT;
IF @address_id IS NOT NULL BEGIN
EXEC insert_user @name, @sex, @date_of_birth, @account_type, @address_id, @user_id OUT;
-- Not sure what this does as @style is not defined anywhere
-- But if it needs a user_id - we have one 
-- EXEC insert_style @style;
END;
RETURN 0;
END
GO
EXEC insert_new_user 'tatiana', 'f', '1970-02-07', 1, 'rue steyls', 13, 'laeken', 'bruxelles', 'be';

Answer 2

这里的逻辑有点奇怪。

SELECT @id_address = IDENT_CURRENT('address')语句在插入语句之后完成。那么，您希望在插入User时如何填充@id_address？

对于您的模型，您需要有一个地址来创建用户。因此，您必须首先创建地址，然后创建用户。

CREATE PROCEDURE insert_new_user(@name VARCHAR(20), @sex CHAR(1), @date_of_birth DATE, @account_type INT, @street VARCHAR(255), @number INT, @locality VARCHAR(255), @city VARCHAR(255), @country_code CHAR(2) )
AS
BEGIN
DECLARE @id_user INT,
@new_id_address INT
-- First step : create address and retrieve the generated address id
-- NB : it means that you will always create an address but not always a user. So you will have some "zombie" addresses in your table. 
-- -> Maybe this statement should be moved into the IF block to create address only if a user has to be created
EXEC insert_address @street, @number, @locality, @city, @country_code, @new_id_address = @id_address OUTPUT
-- 2nd step : optionally create a user.
SELECT @id_user = [user].[id_user]
FROM [user]
WHERE [user].[user_name] = @name
IF (@id_user IS NULL) 
BEGIN
-- Use the above selected id_address as an input parameter to create the user
EXEC insert_user @name, @sex, @date_of_birth, @account_type, @id_address
END
END
GO

为了能够实现这一点，您必须更改insert_address以获得新生成的@id_address

CREATE PROCEDURE insert_address(@street VARCHAR(255), @number INT, @locality VARCHAR(255), @city VARCHAR(255), @country_code CHAR(2), @id_address INT OUTPUT ) AS
BEGIN
INSERT INTO [address]([street], [number], [locality], [city], [country_code])
VALUES(@street, @number, @locality, @city, @country_code)
-- Outputs the generated address id
SELECT @id_address = @@IDENTITY
END
GO

最后，您无需在用户创建过程中选择地址ID

CREATE PROCEDURE insert_user(@name VARCHAR(20), @sex CHAR(1), @date_of_birth DATE, @account_type INT, @id_address INT) AS
BEGIN
DECLARE @position_user INT
INSERT INTO [user]([user_name],[User_Sex], [date_of_birth], [account_type], [id_address])
VALUES(@name, @sex, @date_of_birth, @account_type, @id_address)
-- This statement is useless unless you define @position_user as an OUTPUT parameter
SELECT @position_user = @@IDENTITY
END
GO

Answer 3

感谢所有试图提供帮助的人。

我终于找到了达到预期结果的东西：创建一个用户及其相应的地址。

即使它有效，我希望它在某种程度上不正确，我尝试了此线程上提出的所有解决方案，但无法让它们工作。

这是我目前正在做的事情：

DROP PROCEDURE IF EXISTS insert_user, insert_address, insert_new_user
GO
CREATE PROCEDURE insert_user(@name VARCHAR(20), @sex CHAR(1), @date_of_birth DATE, @account_type INT) AS
BEGIN
DECLARE @id_address int
SELECT @id_address = IDENT_CURRENT('address') + 1
INSERT INTO [user]([user_name],[User_Sex], [date_of_birth], [account_type], [id_address])
VALUES(@name, @sex, @date_of_birth, @account_type, @id_address)
END
go
CREATE PROCEDURE insert_address(@street VARCHAR(255), @number INT, @locality VARCHAR(255), @city VARCHAR(255), @country_code CHAR(2) ) AS
BEGIN
INSERT INTO [address]([street], [number], [locality], [city], [country_code])
VALUES(@street, @number, @locality, @city, @country_code)
END
go
CREATE PROCEDURE insert_new_user(@name VARCHAR(20), @sex CHAR(1), @date_of_birth DATE, @account_type INT, @street VARCHAR(255), @number INT, @locality VARCHAR(255), @city VARCHAR(255), @country_code CHAR(2) )
AS
BEGIN
EXEC insert_user @name, @sex, @date_of_birth, @account_type
EXEC insert_address @street, @number, @locality, @city, @country_code
EXEC insert_style @style
END
GO

EXEC insert_new_user 'tatiana', 'f', '1970-02-07', 1, 'rue steyls', 13, 'laeken', 'bruxelles', 'be'