将存储过程插入具有外键的多个表中

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

步骤如下:

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

这给了我错误:

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

如果我将字段“ id_address”设置为NULL,则该过程正在“运行”,但是[用户]表中的字段“ 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