list constraints(wired way) in the table

Question

hi I create a table as following script. not sure I can see constraints has been create with wired names DF__abcdef__EMP_RES___24927208 , DF__abcdef__EMPLOYEE__239E4DCF

why this commands

ALTER TABLE [dbo].[abcdef] ADD DEFAULT (' ') FOR [EMPLOYEE_ID]

ALTER TABLE [dbo].[abcdef] ADD DEFAULT (' ') FOR [EMP_RES_IND]

categorised as constraints ?

+++++++++++++++++++++++++++++++++++++++

script

CREATE TABLE [dbo].[abcdef](
	[EMPLOYEE_ID] [nchar](10) NOT NULL,
	[EMP_RES_IND] [nchar](1) NOT NULL,
	[RESOURCE_TY] [nchar](5) NOT NULL
	
 CONSTRAINT [abcdef_PK_] PRIMARY KEY CLUSTERED 
(
	[EMPLOYEE_ID] ASC,
	[EMP_RES_IND] ASC,
	[RESOURCE_TY] 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

ALTER TABLE [dbo].[abcdef] ADD  DEFAULT (' ') FOR [EMPLOYEE_ID]
GO

ALTER TABLE [dbo].[abcdef] ADD  DEFAULT (' ') FOR [EMP_RES_IND]
GO

can any one explain it would be great

Answer 1

Hi @Ashwan

I ran your script.

You can use the following query to get information about the object created today.

select * from sys.objects where convert(char(10),create_date,120) = '2023-03-30';

Output:

As you can see, there is a default_constraint.

For more information about sys.objects, you can refer to this link.

https://learn.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-objects-transact-sql?view=sql-server-ver16

The default constraint means that if a default value constraint is defined in the table, when the user inserts a new data row, if the row does not specify data, the system assigns a default value to the column, and if we do not set a default value, the system defaults to NULL.

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Yes, defaults are considered to be constraints in SQL Server. And, yes, that's a little odd, sort of.

As for the naming, best practice is to explicitly name your constraints, like this:

ALTER TABLE [dbo].[abcdef] ADD CONSTRAINT def_EMP_RES_IND DEFAULT (' ') FOR [EMP_RES_IND]