Unikalne ograniczenie na wielu kolumnach
CREATE TABLE [dbo].[user](
[userID] [int] IDENTITY(1,1) NOT NULL,
[fcode] [int] NULL,
[scode] [int] NULL,
[dcode] [int] NULL,
[name] [nvarchar](50) NULL,
[address] [nvarchar](50) NULL,
CONSTRAINT [PK_user_1] PRIMARY KEY CLUSTERED
(
[userID] ASC
)
) ON [PRIMARY]
GO
Jak dodać unikalne ograniczenie dla kolumn fcode, scode, dcode
z t-sql
i/lub management studio
? fcode, scode, dcode
muszą być unikalne razem.
201
4 answers
Używając definicji ograniczenia przy tworzeniu tabeli, możesz określić jedno lub wiele ograniczeń obejmujących wiele kolumn. Składnia, uproszczona z dokumentacji technetu , ma postać:
CONSTRAINT constraint_name UNIQUE [ CLUSTERED | NONCLUSTERED ]
(
column [ ASC | DESC ] [ ,...n ]
)
Dlatego definicja tabeli resuting brzmiałaby:
CREATE TABLE [dbo].[user](
[userID] [int] IDENTITY(1,1) NOT NULL,
[fcode] [int] NULL,
[scode] [int] NULL,
[dcode] [int] NULL,
[name] [nvarchar](50) NULL,
[address] [nvarchar](50) NULL,
CONSTRAINT [PK_user_1] PRIMARY KEY CLUSTERED
(
[userID] ASC
),
CONSTRAINT [UQ_codes] UNIQUE NONCLUSTERED
(
[fcode], [scode], [dcode]
)
) ON [PRIMARY]
254
Author: devmake,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-29 18:22:16
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-12-29 18:22:16
Jeśli tabela jest już utworzona w bazie danych, wtedy możesz dodać unikalne ograniczenie później za pomocą tego zapytania SQL:
ALTER TABLE dbo.User
ADD CONSTRAINT ucCodes UNIQUE (fcode, scode, dcode)
351
Author: marc_s,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-10-26 09:42:47
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-10-26 09:42:47
Można to również zrobić w GUI. Oto przykład dodawania unikalnego ograniczenia wielokolumnowego do istniejącej tabeli.
- pod tabelą kliknij prawym przyciskiem myszy indeksy ->Kliknij/najedź kursorem Nowy indeks->Kliknij indeks bez klastra...
- zostanie podana domyślna nazwa indeksu, ale możesz chcieć ją zmienić. Zaznacz Unique i kliknij Dodaj... button
- Sprawdź kolumny, które chcesz włączyć
Kliknij OK w każdym oknie i gotowe.
34
Author: Tony L.,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-08-18 15:03:13
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-08-18 15:03:13
USE [TSQL2012]
GO
/****** Object: Table [dbo].[Table_1] Script Date: 11/22/2015 12:45:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[seq] [bigint] IDENTITY(1,1) NOT NULL,
[ID] [int] NOT NULL,
[name] [nvarchar](50) NULL,
[cat] [nvarchar](50) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_Table_1] UNIQUE NONCLUSTERED
(
[name] ASC,
[cat] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
0
Author: Muhanned Kamil,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-11-15 16:51:34
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-11-15 16:51:34